Bad results when joining 3 tables

[tHud]

Hi

 

I am trying to join 3 tables. I think I know why I am getting bad results, but I don't know how to rectify the issue.

 

Here are the tables...

 

Companies

comp_id

comp_name

 

Orders

ord_id

comp_id

ord_number

 

Invoices

inv_id

comp_id

inv_number

 

 

I would like to have a table that displays...

 

comp_name -- ord_number -- inv_number

 

I am using comp_id as the common factor, but when I try...

companies.comp_id = orders.ord_id and companies.comp_id = invoices.inv_id

 

I get 'odd' results. I think I see why the results are bad, but don't know what to do about it.

 

Any thoughts on what to do please?

 

Thank you

 

 

[Maq]

Are you actually using JOIN?  Post your query.

[mellis95]

You need to join them on the common keys. For example:

SELECT a.comp_name,b.ord_number,c.inv_number FROM Companies AS a
LEFT JOIN Orders AS b ON b.comp_id=a.comp_id
LEFT JOIN Invoices AS c ON c.comp_id=a.comp_id

 

However, if I am understanding what you are trying to do, you might change your table structures so that the invoice references the order number:

 

 

Companies

comp_id

comp_name

 

Orders

ord_id

comp_id

ord_number

 

Invoices

inv_id

ord_id

inv_number

 

In this case, your query would look like this:

SELECT a.comp_name,b.ord_number,c.inv_number FROM Companies AS a
LEFT JOIN Orders AS b ON b.comp_id=a.comp_id
LEFT JOIN Invoices AS c ON c.ord_id=b.ord_id

 

 

 

 

[tHud]

Thank you. That works for me.