SQL Join - Left Join, but with 4 tables

Good afternoon,

Could someone please help me with this join.

What I have so far

SELECT INArchivedInvoiceItems_0.desc_, INArchivedInvoiceItems_0.desc2, INArchivedInvoiceItems_0.invtype, INArchivedInvoiceHeaders_0.invno, INArchivedInvoiceItems_0.deldate, INArchivedInvoiceItems_0.delno, INArchivedInvoiceItems_0.stcode, INArchivedInvoiceItems_0.depot, INArchivedInvoiceItems_0.pgroup, STStockDetails_0.sref1, INArchivedInvoiceItems_0.lineval, INArchivedInvoiceItems_0.linecost, INArchivedInvoiceHeaders_0.cref

FROM RPWLIVE.INArchivedInvoiceHeaders INArchivedInvoiceHeaders_0, RPWLIVE.INArchivedInvoiceItems INArchivedInvoiceItems_0, RPWLIVE.SLCustomers SLCustomers_0, RPWLIVE.STStockDetails STStockDetails_0

My tables are (to shorten the names down some):

InvoiceHeaders
InvoiceItems
Customers
StockDetails

Everything is fine if I have the Invoice headers, invoice items and customers - this is because every record in invoice item has a corresponding invoice header and vice versa and every customer ref in an invoice header and invoice item will have customer details with in the customers table.

Where it goes to pot is when I add in the stock details table, this is because our sales office can add in a manual sales line onto an invoice and this manual line does not have a corresponding record in the stock details table, but I need these to appear on my report.



Any help would be greatly appreciated.

It would look something like this:

PHP Code: 

SELECT *
FROM
    InvoiceHeaders
        Inner Join InvoiceItems on InvoiceItems.InvoiceID = InvoiceHeaders.InvoiceID
        Inner Join Customers on InvoiceHeaders.CustomerID = Customers.CustomerID
        Right Join StockDetails on StockDetails.StockID = InvoiceItems.StockID
Where
    Something = 'xyz' 


You'd need to fiddle with the syntax slightly, since this is SQL Server and not access (I think access needs some brackets in the joins) but should give you a starter for 10

Hi Kyle,

Thank you very much for this, its worked fine (when I swapped Right to Left Join)

It all works up until I put in my last part of the where statement

Please Login or Register  to view this content.

Once that is added in it again removes the records that have no record held within the stock details table.

Any ideas as to why this is happening?

Dunno without having a look, maybe something like:

Please Login or Register  to view this content.

Hi Kyle,

I think that's done the trick so much as the manual lines are appearing, within the query there were no lines that had the category FSW.

So, I finally have this finished, thank you very much for your help!

Please Login or Register  to view this content.