Join from three tables

Question

I have three tables in the database (with the columns I require in brackets);

Alphadata (Invoice, DateRaised, Amount, Staff)
TL Auth (Invoice)
Agents (Team Leader)

The code I'm currently trying to use to get all these columns into one query is this;

SELECT Alphadata.Invoice, Alphadata.DateRaised, Alphadata.Amount, Alphadata.Staff, Agents.TeamLeader, TlAuth.Invoice

FROM Alphadata 

INNER JOIN TlAuth ON Alphadata.invoice = TlAuth.invoice

INNER JOIN Agents.Alphaname = Alphadata.Staff;

I think I've missed something. But I've got the AlphaData and TL Auth columns populating when I remove the Agents (last line) but the second I re-add that it goes awry.

score 1 · Answer 1 · answered Nov 11 '16 at 08:27

1

You missed the name of table and ON in this line:

INNER JOIN Agents ON Agents.Alphaname = Alphadata.Staff;

answered Nov 11 '16 at 08:27

Roman Marusyk

23,328
24
73
116

I've just added that and seem to be getting Syntax Error (Missing Operator) now. Thinking I might have something wrong somewhere else? – Nik Nov 11 '16 at 08:38

score 0 · Answer 2 · answered Nov 11 '16 at 08:28

You were missing a join condition for the second and third tables. Also, you get good mileage when writing SQL queries if you use table aliases. Note in the corrected query below that I have aliased the three tables in your query. Then, you can refer to the various columns using these alises, and the query is easier to read.

SELECT t1.Invoice,
       t1.DateRaised,
       t1.Amount,
       t1.Staff,
       t2.Invoice,
       t3.TeamLeader,
FROM Alphadata t1                      -- t1, t2 and t3 are aliases, or nicknames
INNER JOIN TlAuth t2                   -- for the actual tables in your query
    ON t1.invoice = t2.invoice
INNER JOIN Agents t3
    ON t3.Alphaname = t1.Staff;

score 0 · Accepted Answer · edited May 23 '17 at 10:32

0

    SELECT Alphadata.Invoice, Alphadata.DateRaised, Alphadata.Amount, Alphadata.Staff, Agents.TeamLeader, TlAuth.Invoice
    FROM (Alphadata 
    INNER JOIN TlAuth ON Alphadata.invoice = TlAuth.invoice)
    INNER JOIN Agents ON Agents.Alphaname = Alphadata.Staff;

Try with the above. If you omit the ON clause this will result in the Cartesian Product of Agents and Alphadata. You can read more about Cartesian Product here.

EDIT 1: From your comment I guess you are using MS Access? If so I found that you need parentheses if you have more than one JOIN - see here. I've added them in the above query. Please try again.

edited May 23 '17 at 10:32

Community

1
1

answered Nov 11 '16 at 08:30

Anton Belev

11,963
22
70
111

Yeah I've copied this and I'm getting a Syntax Error (Missing Operator) now. Not 100% what the issue is as reading this it's aiming at the right stuff! – Nik Nov 11 '16 at 08:48
Are you using MS Access? – Anton Belev Nov 11 '16 at 08:51
@Nik Please try again with the above changes. – Anton Belev Nov 11 '16 at 08:54
Anton. That worked perfectly. Yes next question I'll ensure I point out I'm in Access. Thanks again, have a good friday! – Nik Nov 11 '16 at 08:55
@Nik Glad I helped :) Please upvote my answer if it helps so it appears on the top so others can see it first. – Anton Belev Nov 11 '16 at 08:59
Hey Anton - Gladly if it would let me! Too low a rep. One really quick one... the data is coming back with only records in "TL Auth.Invoice" column that are populated. Not every record has one of these, any chance you could show me quickly how to pull everything through on that column please? NEVERMIND! Figured it out... swapped it from an inner to a left join! – Nik Nov 11 '16 at 09:06

Join from three tables

3 Answers3