combine tables related to each other by a linking table and not to worry if the linking table is missing some data

Question

I have a simple relational database set up similar to the example below

[Customer] --< [CGLink] >-- [Order]

Person:
Alias Name(PK)
A     Bill
B     Ben
C     Bob
D     Jim
E     John

CGLink:
ID(PK) Alias Type
1      A     W
2      A     X
3      B     W
4      B     X
5      B     Y
6      B     Z
7      C     Y
8      E     Z

Group:
Type(PK) Group
W        Double-U
X        Eks
Y        Whai
Z        Zed

And I want to return a set of results like

Alias   Name   Group
A       Bill   Double-U
A       Bill   Eks
B       Ben    Double-U
B       Ben    Eks
B       Ben    Whai
B       Ben    Zed
C       Bob    Whai
D       Jim    
E       John   Zed

As you can see Jim had no associated data in the linking table and I want to avoid having to place a row of dummy data that I can link all people to who don't have a group in order to let me return this data.

The query I am using is

SELECT p.Alias, p.Name, g.Group
FROM Person AS p, Group AS s, CGLink AS l
WHERE (p.Alias=l.Alias
AND l.Type=s.Type)
ORDER BY p.Alias, p.Name;

This returns

Alias   Name   Group
A       Bill   Double-U
A       Bill   Eks
B       Ben    Double-U
B       Ben    Eks
B       Ben    Whai
B       Ben    Zed
C       Bob    Whai
E       John   Zed

Which as you can see, is missing Jim because he had no associated group. How can I obtain the results I really want?

Cheers

score 2 · Answer 1 · answered Dec 14 '10 at 18:51

2

left join

answered Dec 14 '10 at 18:51

Javier

60,510
8
78
126

Additionally, you should probably switch to ANSI join syntax.http://stackoverflow.com/questions/3684259/sql-joins-future-of-the-sql-ansi-standard-where-vs-join – Bill Dec 14 '10 at 19:22
In this case, with Jet/ACE as the target database, it's likely to not make much difference, as Jet/ACE optimizes equivalent implicit and explicit joins exactly the same. I think JOINs are a much better idea in most cases, particularly when the JOINs are on the foreign-key relationships, but there isn't necessarily anything wrong with implicit joins. – David-W-Fenton Dec 19 '10 at 02:17

score 1 · Accepted Answer · answered Dec 14 '10 at 18:53

1

Try using an OUTER join

SELECT p.Alias, p.Name, g.Group
FROM Person AS p
LEFT OUTER JOIN Group AS s ON p.Alias=l.Alias
LEFT OUTER JOIN CGLink AS l ON l.Type=s.Type
ORDER BY p.Alias, p.Name;

answered Dec 14 '10 at 18:53

bobs

21,844
12
67
78

Thanks I found I needed to use parenthesis around the first LEFT JOIN to 'avoid ambiguity'. – Matt Dec 15 '10 at 16:16

combine tables related to each other by a linking table and not to worry if the linking table is missing some data

2 Answers2