SQL Question

Gman · 19 Jun 2007 at 12:08

I've got 3 tables in the query.
Client
ClientSchool
SchoolDetails

Client holds hte general Client details which includes a ClientID
ClientSchool has the foregion Key ClientID and SchoolCode (both foregion keys)
SchoolDetails has the SchoolCode as the key and a schoolName

I'm trying to make a query which looks like so

SELECT
Client.clientName, Client.ClientAddress, ClientNumber, SchoolDetails.schoolName AS School
FROM
Client
LEFT JOIN ClientSchool ON Client.ClientID = ClientSchool.ClientID
LEFT JOIN SchoolDetails ON ClientSchool.SchoolCode = SchoolDetails.schoolName
WHERE (Client.ClientID = '1000123')

This should display ALL the client details from the Client table as well as any SchoolName if it finds a
matching reccord but it doesn't. Any ideas ?

matja · 19 Jun 2007 at 12:25

Nice story, where's the question?

Gman · 19 Jun 2007 at 12:37

updated lol

rpstewart · 19 Jun 2007 at 13:06

Gman said:
This should display ALL the client details from the Client table as well as any SchoolName if it finds a
matching reccord but it doesn't. Any ideas ?

The join path you have will only show a SchoolName if there's a ClientSchool record for the ClientID you have in the WHERE clause.

Have you eyeballed the data to make sure the correct records exist?

Gman · 19 Jun 2007 at 13:21

yep 100% sure I can do the same query just by extending the where clause
i.e.
FROM Client, SchoolDetails, ClientSchool
WHERE SchoolDetails.SchoolCode = ClientSchool.SchoolCode
AND ClientSchool.ClientID = Client.ClientID
AND Client.ClientID = '1000123'

but i'm wanting to do it with the Joins if possible just the only examples I can find for joins just include 2 tables and not 3.

Gman · 19 Jun 2007 at 13:53

no worries sorted it. Finally worked out you can nest the joins so did a nested inner join on the two school tables then a left join on the client and the results of the nested join.