Click here to Skip to main content
13,053,845 members (59,218 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
I have two tables

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



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

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
Comments
jibesh 11-Jan-13 19:42pm
   
is there any relation between SlotId and Day Id? how they are related?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

try the following query
SELECT day, slot from tableDay,tableSlot order by day
  Permalink  
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

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.[^]

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.
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 12 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100