Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select Query to return all rows from one table Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 6:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 4:08 AM
Points: 17, Visits: 33
Hi All,

I have three tables
tPeriod which has PeriodID int, PeriodDiscription char
tTimeTable which has DayID int, StaffID int, SubjectID int, PeriodID int
tSubject which has SubjectID int, Subject char

I join tPeriod and tTimeTable on PeriodID and tTimetable and tSubject on SubjectID

I have a criteria for both StaffID and DayID

I would like to select all PeriodDiscriptions even if tTimeTable does not have it for that criteria

This is what I have so far

SELECT *, dbo.tPeriod.PeriodDiscription, dbo.[tTimeTable2012-13].StaffID, dbo.[tTimeTable2012-13].DayID
FROM dbo.tSubject INNER JOIN
dbo.[tTimeTable2012-13] ON dbo.tSubject.SubjectID = dbo.[tTimeTable2012-13].SubjectID RIGHT OUTER JOIN
dbo.tPeriod ON dbo.[tTimeTable2012-13].PeriodID = dbo.tPeriod.PeriodID
WHERE (dbo.[tTimeTable2012-13].StaffID = 18) AND (dbo.[tTimeTable2012-13].DayID = 1)

But this only selects where periodIDs are equal and I need all of them.

IE

Period1 English
Period2 Maths
Period3 Null
Period4 Break
Period5 Null
Period6 Science

Etc

Any help would be great

JB
Post #1372673
Posted Monday, October 15, 2012 6:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:00 AM
Points: 2,433, Visits: 7,501
Hello and welcome to SSC,

If you could knock up some sample data and DDL scripts, then your expected results based on the sample data provided that would be extremely useful in allowing people to help you.

Have a read through this link --> http://www.sqlservercentral.com/articles/Best+Practices/61537/, if you're unsure how best to lay this information out. Remember that if your sample data and DDL script is readily consumable, then the volunteers for this forum are much more likely to take time out from their work to give you a hand.

Thanks.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1372680
Posted Monday, October 15, 2012 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,836, Visits: 5,066
The style you used to write your query I call "noodles" as it takes to much time to read top understand what you're are doing.
Bit messy, really.
I think the following change should make it work:

SELECT *, dbo.tPeriod.PeriodDiscription, dbo.[tTimeTable2012-13].StaffID, dbo.[tTimeTable2012-13].DayID
FROM dbo.tSubject
INNER JOIN dbo.[tTimeTable2012-13] ON dbo.tSubject.SubjectID = dbo.[tTimeTable2012-13].SubjectID
AND (dbo.[tTimeTable2012-13].StaffID = 18) AND (dbo.[tTimeTable2012-13].DayID = 1)
RIGHT OUTER JOIN dbo.tPeriod ON dbo.[tTimeTable2012-13].PeriodID = dbo.tPeriod.PeriodID

However, I would rewrite it to something like:
SELECT *
FROM dbo.tPeriod AS P
LEFT JOIN dbo.[tTimeTable2012-13] AS T
ON T.PeriodID = P.PeriodID
AND T.StaffID = 18
AND T.DayID = 1
LEFT JOIN dbo.tSubject AS S
ON S.SubjectID = S.SubjectID

Is it easier to see what query is doing now, or am I just too picky?


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1372699
Posted Monday, October 15, 2012 6:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:26 PM
Points: 223, Visits: 1,719
You have incorrect conditions in where clause, that eliminates value which you want to see. Try this, and correct conditions in where clause to your needs:
SELECT *, dbo.tPeriod.PeriodDiscription, dbo.[tTimeTable2012-13].StaffID, dbo.[tTimeTable2012-13].DayID
FROM dbo.tSubject INNER JOIN
dbo.[tTimeTable2012-13] ON dbo.tSubject.SubjectID = dbo.[tTimeTable2012-13].SubjectID RIGHT OUTER JOIN
dbo.tPeriod ON dbo.[tTimeTable2012-13].PeriodID = dbo.tPeriod.PeriodID
WHERE ((dbo.[tTimeTable2012-13].StaffID = 18) AND (dbo.[tTimeTable2012-13].DayID = 1)) or
(dbo.[tTimeTable2012-13].StaffID is null)

Post #1372705
Posted Tuesday, October 16, 2012 1:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 4:08 AM
Points: 17, Visits: 33
Many thanks for the help below is what worked for me

SELECT *, dbo.tPeriod.PeriodDiscription, dbo.[tTimeTable2012-13].StaffID, dbo.[tTimeTable2012-13].DayID
FROM dbo.tSubject
INNER JOIN dbo.[tTimeTable2012-13] ON dbo.tSubject.SubjectID = dbo.[tTimeTable2012-13].SubjectID
AND (dbo.[tTimeTable2012-13].StaffID = 18) AND (dbo.[tTimeTable2012-13].DayID = 1)
RIGHT OUTER JOIN dbo.tPeriod ON dbo.[tTimeTable2012-13].PeriodID = dbo.tPeriod.PeriodID


JB
Post #1373055
Posted Tuesday, October 16, 2012 4:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,836, Visits: 5,066
Jay B-317395 (10/16/2012)
Many thanks for the help below is what worked for me

SELECT *, dbo.tPeriod.PeriodDiscription, dbo.[tTimeTable2012-13].StaffID, dbo.[tTimeTable2012-13].DayID
FROM dbo.tSubject
INNER JOIN dbo.[tTimeTable2012-13] ON dbo.tSubject.SubjectID = dbo.[tTimeTable2012-13].SubjectID
AND (dbo.[tTimeTable2012-13].StaffID = 18) AND (dbo.[tTimeTable2012-13].DayID = 1)
RIGHT OUTER JOIN dbo.tPeriod ON dbo.[tTimeTable2012-13].PeriodID = dbo.tPeriod.PeriodID


JB


Yeah, tasty as Singaporean noodles, but hard to read for some dyslexic people like myself ...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1373132
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse