SQL SELECT from TABLE B based on column but grab all rows based on TABLE A ID

debugcn Published at Dev

expenguin

So I've been hit with a dozy this morning.

I've got two tables listed below.

TABLE A
InvoiceNo | Total
000001    | $100
000002    | $50
000003    | $200
etc..

TABLE B
InvoiceNo | ItemCode
000001    | Item-A
000001    | Item-B
000001    | Item-C
000002    | Item-A
000003    | Item-B
000003    | Item-D
etc...

I need to select out of table 2 where ITEM-B exists, but also grab all other items based on the InvoiceNo. So it should grab ItemCode > InvoiceNo > All Items based on that InvoiceNo

OUTPUT
InvoiceNo | Total | ItemCode
000001    | $100  | Item-A
000001    | $100  | Item-B
000001    | $100  | Item-C
000003    | $200  | Item-B
000003    | $200  | Item-D

The query I'm currently working with is:

SELECT a.InvoiceNo, a.Total, b.ItemCode FROM TableA a
LEFT JOIN TABLE B b ON a.InvoiceNo = b.InvoiceNo
WHERE b.ItemCode = 'Item-B'

This generates the following output

INCORRECT OUTPUT
InvoiceNo | Total | ItemCode
000001    | $100  | Item-B
000003    | $200  | Item-B

I'm not quite sure how to approach this, any clues or assistance would be very appreciated.

Cheers

John Woo

There are many ways to do this. One way is by using EXISTS

SELECT  a.InvoiceNo, A.Total, B.ItemCode
FROM    TableA a
        INNER JOIN TableB b
            ON a.InvoiceNo = b.InvoiceNo
WHERE   EXISTS (SELECT 1
                FROM TableB c
                WHERE b.InvoiceNo = c.InvoiceNo
                        AND c.ItemCode = 'Item-B')

Here's a Demo.

Another way is by using INNER JOIN on a subquery which only get all invoices having ItemB

SELECT  a.InvoiceNo, A.Total, B.ItemCode
FROM    TableA a
        INNER JOIN TableB b
            ON a.InvoiceNo = b.InvoiceNo
        INNER JOIN (SELECT InvoiceNo FROM TableB c WHERE ItemCode = 'Item-B') c
            ON a.InvoiceNo = c.InvoiceNo

Here's a Demo.

Collected from the Internet

Please contact [email protected] to delete if infringement.