CODEWITHSUNDEEP
sql-serverjoin
Pau808
Pau808

Reputation: 213

Having issues returning 4 columns by joining 3 tables and using aliases in SQL?

I am still very new at SQL and was unable to find an answer on here...I am supposed to write a SELECT statement that returns 4 columns from 3 tables using aliases and then assign correlation names to the tables... But I am getting an error on the WHERE clause and I am not quite sure how to join the 3 tables to get the results I am looking for. Also, it did not specify which type of JOIN to do.

Here is my code:

 SELECT VendorName AS [Vendor], InvoiceDate AS [Date], InvoiceNumber AS [Number], AccountItemDescription AS [Description]
    FROM Vendors AS v JOIN Invoices AS i 
    ON v.VendorID = i.InvoiceID
    JOIN InvoiceLineItems AS l JOIN GLAccounts AS g 
    WHERE l.InvoiceLineItems = g.GLAccounts
    ORDER BY Vendor, Description;-- Unfinished

Getting this error:

Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'WHERE'.

Any tips would be GREATLY appreciated...

Upvotes: 1

Views: 81

Answers (2)

DWallace
DWallace

Reputation: 310

It helps to be careful in how you format your code. Every join should have an on clause. You are missing them on your InvoiceLineItems and GLAccounts joins.

Additionally, your on clause will typically link the primary key of one table to the foreign key in another; joining Vendors to Invoices by equating vendorID to invoiceID is not going to give you accurate results. Those IDs have no direct correlation. You want to link vendorID from one table to the vendorID in the other. I'm not sure how your fields are named; it could be i.vendorID = v.vendorID or it could be something like i.vendorID = v.id. Follow the same pattern for your other joins.

You'll end up with something like this:

SELECT VendorName AS [Vendor], 
    InvoiceDate AS [Date], 
    InvoiceNumber AS [Number],
    AccountItemDescription AS [Description]
FROM Vendors AS v 
JOIN Invoices AS i ON i.VendorID = v.VendorID
JOIN InvoiceLineItems AS l ON l.InvoiceID = i.InvoiceID
JOIN GLAccounts AS g ON l.GLAccountID = g.GLAccountID
ORDER BY Vendor, Description;

Upvotes: 5

Jorge Campos
Jorge Campos

Reputation: 23361

Change it to:

 SELECT VendorName AS [Vendor], InvoiceDate AS [Date], 
        InvoiceNumber AS [Number],   AccountItemDescription AS [Description]
FROM Vendors AS v 
       JOIN Invoices AS i ON v.VendorID = i.InvoiceID
       JOIN InvoiceLineItems AS l  --YOU MISS AN ON CLAUSE HERE
       JOIN GLAccounts AS g ON l.InvoiceLineItems = g.GLAccounts
ORDER BY Vendor, Description;

You add the joining in an old style leaving the new one without the ON clause which was generating the error

Since I don't know your table structure I can't suggest what to put there.

Upvotes: 0

Related Questions