Select Query to return all rows from one table

Question

Select Query to return all rows from one table

Jay B-317395

SSC Enthusiast

Points: 157
More actions
October 15, 2012 at 6:05 am

#283265

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

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/%5B/url%5D, 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.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
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/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

e4d4 SSCertifiable Points: 5777 More actions · Answer 3

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)

Jay B-317395 SSC Enthusiast Points: 157 More actions · Answer 4

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

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 :hehe:...

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

How to post your question to get the best and quick help[/url]