CODEWITHSUNDEEP
sqlt-sql
D L
D L

Reputation: 5

Select all transactions that contain a specific item?

Good Morning,

The question I am running into involves a database with transactions. The transactions are stored as lines by each item sold. What would the code be if I wanted to select all transactions which contained item 11222 so I can see what other items are bought with it?

I was using:

SELECT [Transaction]
, [Date]
, [Item]
, [Register]
, [Location]

WHERE [Item] = 11222
FROM "Transactions"

Then looking up each transaction with date and location separately to see, however my request is now to look at thousands of transactions. Is there a way in SQL to conditionally pull all lines, even if the specific line doesn't contain the requested item #?

Example of how the data is in the table:

Transaction Date        Item    Register    Location

123         1/1/2019    11222   1           15
123         1/1/2019    45663   1           15
124         1/1/2019    77433   1           15
124         1/1/2019    11222   1           15
124         1/1/2019    66092   1           15
125         1/1/2019    66933   1           15
125         1/1/2019    77433   1           15
126         1/1/2019    11222   1           15
126         1/1/2019    82991   1           15
127         1/1/2019    88392   1           15
127         1/1/2019    88492   1           15

Upvotes: 0

Views: 885

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

I would phrase this as exists:

SELECT t.*
FROM Transactions t
WHERE EXISTS (SELECT 1
              FROM Transactions t2
              WHERE t2.transaction = t.transaction AND
                    t2.Item = 11222
             )
ORDER BY t.transaction;  -- keep all the rows for a given transaction together

Upvotes: 1

Related Questions