sdsd sds sdsd sds - 7 months ago 11
SQL Question

joins in table is not working properly

I have below three tables which are joined among themselves. The tables names are

1) i IOA_INVOICE_LINE
2) IOA_INVOICE_LINE_NOTES
3) IOA_INV_LINE_NOTE_MAP


An image is attached which will graphically shows you the relationship. I am trying to get the count of inv_line_note_id from IOA_INV_LINE_NOTE_MAP table with respect to each id column in the ioa_invoice_line table. Please advise how to get the count of column inv_line_note_id of IOA_INV_LINE_NOTE_MAP table with context to id column in the ioa_invoice_line table .

As advised I have added sample data:

IOA_INVOICE_LINE :-

ID VERSION
1234 Abc


IOA_INVOICE_LINE_NOTES :-

ID INLI_ID NOTES
345 1234 Aqou


INV_LINE_NOTE_MAP :-

ID INV_LINE_NOTE_ID ATTACHEMENT
23 345 rtfffr
24 345 fhgygg


below is the query which was implemented but its not working as it is showing the result as 0 which should note be as there i the expected value should 2 as the there is total 2 rows in the table INV_LINE_NOTE_MAP with correspond to column id 1234 of IOA_INVOICE TABLE please advise how to get the total count of column inv_line_note_id of IOA_INV_LINE_NOTE_MAP table with context to id column in the ioa_invoice_line table

select ioa_invoice_line.id, count(IOA_INV_LINE_NOTE_MAP.attachment_blob_id) as totalAttachment
from ioa_invoice_line
left outer join ioa_invoice_line_notes
on ioa_invoice_line.id = ioa_invoice_line_notes.inli_id
left outer join IOA_INV_LINE_NOTE_MAP
on ioa_invoice_line_notes.id = IOA_INV_LINE_NOTE_MAP.id
where ioa_invoice_line.id =1234
GROUP BY ioa_invoice_line.id;


have also attached the image that will show the relationship
image to show the relationship among three tables

Answer

Your last join condition is wrong as it compares two primary keys (both are id):

Instead:

select ioa_invoice_line.id, count(IOA_INV_LINE_NOTE_MAP.id) as totalAttachment
from ioa_invoice_line
     left outer join ioa_invoice_line_notes 
              on ioa_invoice_line.id = ioa_invoice_line_notes.inli_id
     left outer join IOA_INV_LINE_NOTE_MAP 
              on ioa_invoice_line_notes.id = IOA_INV_LINE_NOTE_MAP.INV_LINE_NOTE_ID
              where  ioa_invoice_line.id =1234 
GROUP BY ioa_invoice_line.id;

Notice the INV_LINE_NOTE_ID.

I would also suggest to count IOA_INV_LINE_NOTE_MAP.id if your interest is to count records. If however, you want to exclude any records for which attachment_blob_id is null (can it be?), then keep the count as you had it.

Addendum: conditions on count

If you want to only get records where count(IOA_INV_LINE_NOTE_MAP.id) > 0 then turn the left outer joins into inner joins:

select ioa_invoice_line.id, count(IOA_INV_LINE_NOTE_MAP.id) as totalAttachment
from ioa_invoice_line
     inner join ioa_invoice_line_notes 
              on ioa_invoice_line.id = ioa_invoice_line_notes.inli_id
     inner join IOA_INV_LINE_NOTE_MAP 
              on ioa_invoice_line_notes.id = IOA_INV_LINE_NOTE_MAP.INV_LINE_NOTE_ID
              where  ioa_invoice_line.id =1234 
GROUP BY ioa_invoice_line.id;

But if your condition would be something else on the count, then you can use the having clause after the group by. It works like a where, but acts after the grouping has happened, and you can reference aggregates like count, etc.

For instance, if you would be interested only in cases where count(IOA_INV_LINE_NOTE_MAP.id) > 2, then you would write:

select ioa_invoice_line.id, count(IOA_INV_LINE_NOTE_MAP.id) as totalAttachment
from ioa_invoice_line
     inner join ioa_invoice_line_notes 
              on ioa_invoice_line.id = ioa_invoice_line_notes.inli_id
     inner join IOA_INV_LINE_NOTE_MAP 
              on ioa_invoice_line_notes.id = IOA_INV_LINE_NOTE_MAP.INV_LINE_NOTE_ID
              where  ioa_invoice_line.id =1234 
GROUP BY ioa_invoice_line.id
HAVING count(IOA_INV_LINE_NOTE_MAP.id) > 2;