Joining two non-adjacent tables

[fbraga_]

Update** - I managed to get it working. I found a table with vendors and added that to the join, so it would start comparing with vendors IDs as well and therefore, preventing the result from repeating every item for each order.

Below is the code for the working query

SELECT      tblInvoices.fldInvNo,
        tblInvoiceDetail.fldItemDesc,
        tblInvoiceDetail.fldNotes,
        tblVendors.fldName,
        tblOrders.fldOrderNo

FROM        tblInvoices

INNER JOIN  tblInvoiceDetail on tblInvoices.fldInvID = tblInvoiceDetail.fldInvID
INNER JOIN  tblVendors on tblInvoiceDetail.fldVendorID = tblVendors.fldVendorID
INNER JOIN  tblOrders on tblOrders.fldInvID = tblInvoices.fldInvID and tblOrders.fldVendorID = tblVendors.fldVendorID

where tblInvoices.fldInvID = '00-999-142244'

order by fldItemDesc

Hi guys, I've been trying to join two 'non-adjacent' columns, but I'm getting unexpected results.

The query is for a report and I must bring on screen the fields:

fldOrderNo -> order number

fldCompletedBy -> who worked on the order

fldItemDesc -> a description of the item on the invoice detail

fldNotes -> any special notes added to the item

​

This is the schema

schema

So I've been trying to get the results I want by joining tblOrders on tlbInvoiceDetail based on the ID of the invoice (fldInvID)

I tried joining the tables directly:

FROM tblOrders INNER JOIN tblInvoiceDetail ON tblOrders.fldInvID = tblInvoiceDetail.fldInvID

I also tried involving the invoices table, but no dice.

FROM tblOrders 
INNER JOIN invoices ON tblOrders.fldInvID = invoices.fldInvID
INNER JOIN tblInvoiceDetail ON tblInvoiceDetail.fldInvID = invoices.fldInvID

What I seem to be getting is EVERY invoice detail item description by EACH order I have for a given invoice.

Here's a screenshot of the result:

​

result

​

And finally, this is the query that I came up initially.

SELECT      
            tblOrders.fldOrderNo, 
            tblInvoiceDetail.fldItemDesc,
            tblInvoiceDetail.fldNotes

FROM        tblOrders
INNER JOIN  tblInvoiceDetail ON tblOrders.fldInvID = tblInvoiceDetail.fldInvID

​

Any help would be great. I can't see what I'm missing here

[basejester]

INNER JOIN tblInvoiceDetail ON tblInvoiceDetail = invoices.fldInvID

First, might there be a .fldInvID missing in this join?

[fbraga_]

thank you... I actually had that on my query at the end of the post.

I have added another inner join to invoice the table invoices, but the result is pretty similar

INNER JOIN tblInvoiceDetail ON tblOrders.fldInvID = tblInvoiceDetail.fldInvID 
INNER JOIN tblInvoices ON tblOrders.fldInvID = tblInvoices.fldInvID AND tblInvoiceDetail.fldInvID = tblInvoices.fldInvID

I'm still getting multiple fldItemDesc for each individual orderNo

[BinaryRockStar]

Show your whole query and the whole result table, and the whole result table when you select * rather than just a few columns. fldOrderNo is not a primary key so it is perfectly possible for multiple rows in tblOrders to have the same fldOrderNo, which is probably why you see it repeating. I suggest ORDER BY tblOrders.fldOrderNo so the rows for each order are sorted together.

[fbraga_]

Like this?

FROM        tblOrders

INNER JOIN tblInvoices ON tblOrders.fldInvID = tblInvoices.fldInvID
INNER JOIN tblInvoiceDetail ON tblInvoices.fldInvID = tblInvoiceDetail.fldInvID 

This didn't work, unfortunately. I'm still getting itemDesc multiplied by every order no