-2

I'm trying to join 3 tables where I only need the rows from table 1 but I'm getting too many records.

I have the following table structure and all 3 tables must be joined:

InvoiceDetailsTable Columns:
IDE_invoiceId, IDE_companyId, IDE_InvoiceDetailsId, IDE_price etc...
---data=>
InvoiceId1, companyId1
....
InvoiceId1001, companyId1
InvoiceId1001, companyId2

InvoiceTable Columns:
I_invoiceId, I_shopId
---data=>
InvoiceId1, shopId1
...
InvoiceId1001, shopId5
InvoiceId1001, shopId6

ShopTable Columns:
S_shopId, S_companyId
--data=>
shopId5, companyId1
shopId6, companyId2
....

In the above situation the invoiceId in InvoiceTable is not unique. It is unique in combination with the shopId.

I have several cases where one InvoiceDetail belongs to a specific Invoice but I must take in account the correct shop. But I don't have the shopId in InvoiceDetails. I do however have the correct CompanyId and this should be good too in order to uniquely identify the InvoiceDetail.

I have tried several queries but can't seem to find a correct one:

select * from invoiceDetailsTable IDE where IDE_price is null;

The above query returns 3 records.

The query below returns 5 records because of the same InvoiceId 'InvoiceId1001'.

select * from invoiceDetailsTable IDE 
left join invoiceTable I on IDE.IDE_invoiceId = I.I_invoiceId 
left join ShopTable S on I.I_shopId = S.S_shopId 
where IDE.IDE_price is null;

select * from invoiceDetailsTable IDE 
left join invoiceTable I on IDE.IDE_invoiceId = I.I_invoiceId 
left join ShopTable S on I.I_shopId = S.S_shopId and IDE.IDE_companyId = S.S_companyId
where IDE.IDE_price is null;

Unfortunately I'm getting double records when an InvoiceId belongs to more then 1 shopId/companyId. How to solve this?

philipxy
  • 14,867
  • 6
  • 39
  • 83
user1841243
  • 1,663
  • 2
  • 19
  • 35
  • 3
    Aliases like `a` is for `invoiceDetailsTable` and `b` is for `invoiceTable` aren't helpful for you or others that want to read your code. Use meaningful and *consistent* aliases for your objects; `ID` for *Invoice Details*, `I` for *Invoice* for example. I suggest a read of [Bad Habits to Kick : Using table aliases like (a, b, c) or (t1, t2, t3)](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3). – Thom A Jun 13 '22 at 13:27
  • Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) – philipxy Jun 13 '22 at 13:48
  • Is `select *` how you are querying these tables - you need every column from every table? Or do you only need columns from "table 1"? – Stu Jun 13 '22 at 13:49
  • The third query looks like it should work. Proper sample data and expected results would help – Charlieface Jun 13 '22 at 14:03
  • @philipxy thanks but that question is different. It has 2 tables with multiple occurences of userid's. I have 1 table (invoice) with a combined key while another table (invoicedetails) has a partial key to the invoice table. – user1841243 Jun 13 '22 at 14:07
  • It is the same issue. You are joining & getting multiple rows of one table per 1 from another & aggregating over that explosion. The solution is to write separate appropriate aggregations & join them together always on a key of 1 table, although there are other ways as well. This is a faq. PS [mre] – philipxy Jun 13 '22 at 14:24

1 Answers1

0

If I understand correctly that invoiceDetailsTable.IDE_companyId maps to I_shopId/S_shopId in the other tables, then you want this:

SELECT * 
FROM invoiceDetailsTable IDE 
LEFT JOIN invoiceTable I on IDE.IDE_invoiceId = I.I_invoiceId 
    AND IDE.IDE_companyId = I.I_shopId
LEFT JOIN ShopTable S on I.I_shopId = S.S_shopId 
WHERE IDE.IDE_price is null;

Otherwise, the schema is broken and must be fixed.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • ShopId is different from companyId. a shopId belongs to companyId but it's a different ID. I was wondering if the schema was broken as well... – user1841243 Jun 13 '22 at 13:50
  • If there's a way to discover a shopID based on a companId than it might still be possible to write the query, but not from only these tables. – Joel Coehoorn Jun 13 '22 at 16:40