Need SQL Query for this following problem

Question

5.00/5 (1 vote)

See more:

I have two tables

VB

day id  day
1   Monday
2   Tuesday
3   Wednesday
4   Thursday
5   Friday
6   Saturday
7   Sunday

CSS

slot id slot
1   9:00 - 12:00
2   10:00 - 13:00
3   11:00 - 14:00
4   12:00 - 15:00
5   13:00 - 16:00
6   14:00 - 17:00

i need to write a query to get the following result

CSS

Monday  9:00 - 12:00
Monday      10:00 - 13:00
Monday      11:00 - 14:00
Monday      12:00 - 15:00
Monday      13:00 - 16:00
Monday      14:00 - 17:00
Tuesday      9:00 - 12:00
Tuesday     10:00 - 13:00
Tuesday     11:00 - 14:00
Tuesday     12:00 - 15:00
Tuesday     13:00 - 16:00
Tuesday     14:00 - 17:00
Wednesday   9:00 - 12:00
Wednesday   10:00 - 13:00
Wednesday   11:00 - 14:00
Wednesday   12:00 - 15:00
Wednesday   13:00 - 16:00
Wednesday   14:00 - 17:00
Thursday    9:00 - 12:00
Thursday    10:00 - 13:00
Thursday    11:00 - 14:00
Thursday    12:00 - 15:00
Thursday    13:00 - 16:00
Thursday    14:00 - 17:00

Seems simple though, but i just cant get my mind working

Posted 11-Jan-13 13:13pm

Hasham Ahmad

Add a Solution

Comments

Jibesh 11-Jan-13 19:42pm

is there any relation between SlotId and Day Id? how they are related?

2 solutions

Solution 2

Solutioin of jibesh is good, but i prefer to use JOINs[^]. By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft SQL Server should use data from one table to select the rows in another table.[^]

SQL

SELECT d.day, s.slot
FROM tableDay AS d INNR JOIN tableSlot AS s ON d.[day id] = s.[slot id]
ORDER BY d.[day id]

Why to use joins?[^]
In earlier versions of Microsoft® SQL Server™ , left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.

Posted 12-Jan-13 10:37am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jibesh · Accepted Answer · 2013-01-11T13:50:00

Solution 1

try the following query

SQL

SELECT day, slot from tableDay,tableSlot order by day

Posted 11-Jan-13 13:50pm

Jibesh

Comments

CHill60 11-Jan-13 20:01pm

Only improvement I can add is "order by day, slot"

Jibesh 11-Jan-13 20:03pm

Thanks for catching that, I didnt check that because OP didnt share too much on the data type.

CHill60 11-Jan-13 22:21pm

Agreed ... almost makes you anticipate the next question with the next table really ;-p