Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » Select Query to return all rows from one...
Add to briefcase

Select Query to return all rows from one table Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Jay B-317395
Posted Monday, October 15, 2012 6:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 09, 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
Cadavre
Posted Monday, October 15, 2012 6:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486
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
Eugene Elutin
Posted Monday, October 15, 2012 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 2,534, Visits: 4,351
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
e4d4
Posted Monday, October 15, 2012 6:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 150, Visits: 1,027
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
Jay B-317395
Posted Tuesday, October 16, 2012 1:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 09, 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
Eugene Elutin
Posted Tuesday, October 16, 2012 4:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 2,534, Visits: 4,351
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.