CODEWITHSUNDEEP
sqlms-access
Nik
Nik

Reputation: 41

Join from three tables

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

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.

Upvotes: 0

Views: 50

Answers (3)

Anton Belev
Anton Belev

Reputation: 13503

    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.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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;

Upvotes: 0

Roman Marusyk
Roman Marusyk

Reputation: 24569

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

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

Upvotes: 1

Related Questions