Nik Nik - 23 days ago 7
SQL Question

Join from three tables

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.

Answer
    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.

Comments