right or left join ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

right or left join ?

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-10-19 : 08:12:37

Hi, i havea couple of tables I am joining together, what I am trying to acheive is a list of all establishments regardless of whether there is a teach linked to that establishment. so far I have :

select Establishments.EstablishmentID, RTRIM(dbo.Staff.Title) + ' ' + RTRIM(dbo.Staff.Forename) + ' ' + RTRIM(dbo.Staff.Surname) AS Deputy
From Appointments
INNER JOIN StaffTypes ON Appointments.StaffTypeID=StaffTypes.StaffTypeID
INNER JOIN Staff ON Appointments.StaffID=Staff.StaffID
LEFT OUTER JOIN Establishments ON Appointments.EstablishmentID=Establishments.EstablishmentID
WHERE Appointments.Active = 'Y' AND StaffTypes.StaffTypeDesc = 'Teacher'

However this only brings back establishments where there is a teacher.
how can I bring back all establishments , if there is a teach display the name else display a blank.....

thanks,Jamie

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-19 : 08:17:24

try this:

From Establishments
left join
(Appointments INNER JOIN StaffTypes ON Appointments.StaffTypeID=StaffTypes.StaffTypeID
INNER JOIN Staff ON Appointments.StaffID=Staff.StaffID)
ON Appointments.EstablishmentID=Establishments.EstablishmentID

Go with the flow & have fun! Else fight the flow

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-10-19 : 08:18:17

Then you should have establishments as the table next to the word from and have a left outer to appointments.

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-10-19 : 08:19:28

Sprirt beat me to it again! - do you ever get any work done dude?

Duane.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-10-19 : 08:21:47

no that doesn't work.
so now I have :

select Establishments.EstablishmentID, RTRIM(dbo.Staff.Title) + ' ' + RTRIM(dbo.Staff.Forename) + ' ' + RTRIM(dbo.Staff.Surname) AS Deputy
From Establishments
left join
(Appointments INNER JOIN StaffTypes ON Appointments.StaffTypeID=StaffTypes.StaffTypeID
INNER JOIN Staff ON Appointments.StaffID=Staff.StaffID)
ON Appointments.EstablishmentID=Establishments.EstablishmentID
WHERE Appointments.Active = 'Y' AND StaffTypes.StaffTypeDesc = 'Teacher'

still only equal establishmentID's are displayed.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-19 : 08:30:53

ditch: yes i do

but today i'm home... i'm a bit sick and i don't want anyone to catch anything at work.
going to the doctor now so you can go and help out more...

how about:

From Establishments
left join
(select Staff.Title, Staff.ForeName, Staff.Surname, Appointments.EstablishmentID From Appointments INNER JOIN StaffTypes ON Appointments.StaffTypeID=StaffTypes.StaffTypeID
INNER JOIN Staff ON Appointments.StaffID=Staff.StaffID) t1
ON t1.EstablishmentID = Establishments.EstablishmentID

Go with the flow & have fun! Else fight the flow

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-10-19 : 08:45:54

no, that gives the same.
how strange.

the data looks like this :

establishments table :
establishmentID
1
2
3
4
5

Appointments table
EstablishmentID Active StaffID StaffTypeID
1 y 12 3
1 y 34 4
2 y 35 3
2 n 36 3

so with the where clause I am saying active = y and stafftypeid = 3
the appointments talbe will only display 2 records.
but I need to display all establishment records so the output should be :

establishmentId StaffID
1 12
2 35
3
4
5

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-19 : 09:08:00

select Establishments.EstablishmentID, RTRIM(dbo.Staff.Title) + ' ' + RTRIM(dbo.Staff.Forename) + ' ' + RTRIM(dbo.Staff.Surname) AS Deputy
From Establishments
left join
(Appointments INNER JOIN StaffTypes ON Appointments.StaffTypeID=StaffTypes.StaffTypeID
INNER JOIN Staff ON Appointments.StaffID=Staff.StaffID AND Appointments.Active = 'Y' AND StaffTypes.StaffTypeDesc = 'Teacher')
ON Appointments.EstablishmentID=Establishments.EstablishmentID

Putting an entry in the where clause referencing an outer entity turns the outer join into an inner join (unless you have an "or is null" clause).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-19 : 09:15:17

1) don't use parenthesis to group joins, in general. if you need to specify an "order of operation" for mixing INNER and OUTER joins, it is usually an indicator that you should use subqueries or derived tables ]i.e., SELECT .. FROM (SELECT) A ] It will almost always make your logic much more cleaner to break up your SELECT into smaller parts and put them all together.

2) if you need a list of Establishments, and from there other data that may or not match, remember you will always start from something like this:

SELECT ...
FROM Establishments
LEFT OUTER JOIN ....something .....

3) now, put that aside, and write a query that returns everything else, and be sure you return an EstablishmentID to join:


SELECT 
   Appointments.EstablishmentID, 
   RTRIM(dbo.Staff.Title) + ' ' + RTRIM(dbo.Staff.Forename) + ' ' + RTRIM(dbo.Staff.Surname) AS Deputy
FROM
   Appointments
INNER JOIN 
   StaffTypes ON Appointments.StaffTypeID=StaffTypes.StaffTypeID
INNER JOIN 
   Staff ON Appointments.StaffID=Staff.StaffID
WHERE 
   Appointments.Active = 'Y' AND StaffTypes.StaffTypeDesc = 'Teacher'

4) make sure the above query returns what you want. Even though it doens't list ALL establishments, make sure it returns data correctly for those that do have appointments.

5) finally, join them together:


SELECT E.EstablishmentID, B.Deputy
FROM
  Establishments E
LEFT OUTER JOIN
   (sql from step 3) B
ON
  E.EstablishmentID = B.EstablishmentID

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-19 : 09:15:44

Also try

select Establishments.EstablishmentID, coalesce(RTRIM(dbo.Staff.Title) + ' ' + RTRIM(dbo.Staff.Forename) + ' ' + RTRIM(dbo.Staff.Surname), '') AS Deputy
From Establishments
left join Appointments
ON Appointments.EstablishmentID=Establishments.EstablishmentID
AND Appointments.Active = 'Y'
left JOIN StaffTypes
ON Appointments.StaffTypeID=StaffTypes.StaffTypeID
AND StaffTypes.StaffTypeDesc = 'Teacher'
left JOIN Staff
ON Appointments.StaffID=Staff.StaffID

p.s. - no point having the StaffTypes lookup table if you are going to hard code the StaffTypeDesc in queries.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-10-19 : 09:17:34

I see nr.
thank you for the SQL, that works perfectly.

ps, Is this bad db design or standard practice when working with such data ?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-19 : 09:21:42

Well the main point of a lookup table is to be able to change the description without affecting any code - you lose that if you use the description in the code.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-10-19 : 09:24:03

sorry guys, another little modification.,...
what If I also wnat a field for another stafftype, so the data will look like :

EstablshmentID StaffType1 Staff1 StaffType2 Staff2
1 3 Jamie 4 Bob
2 3 Steve
3 4 Mike
4
5 4 chris

?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-19 : 09:29:20

as mentioned, use the subquery approach work on getting the subquery to return what you need, in the format you desire, without worrying about the "returning all establishments" requirement until the last step.

do it one step at a time.

Work on as few tables as you can in each query, break it into parts, and then put it together in the end.

- Jeff

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-10-19 : 09:38:27

Amazing! thanks Jeff, thats a handy technique !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources