Click here to Skip to main content
15,904,823 members
Please Sign up or sign in to vote.
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
Comments
Jibesh 11-Jan-13 19:42pm    
is there any relation between SlotId and Day Id? how they are related?

try the following query
SQL
SELECT day, slot from tableDay,tableSlot order by day
 
Share this answer
 
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
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]



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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900