Complex SQL Query in one table

Question

I have a single table as below and I want the Products which are having same Lot Numbers in different countries. I need all the records.

Table:

╔════════════╦════════════╦═════════╗
║ Product ID ║ Lot Number ║ Country ║
╠════════════╬════════════╬═════════╣
║          1 ║ L01        ║ US      ║
║          2 ║ L02        ║ US      ║
║          3 ║ L01        ║ UK      ║
║          4 ║ L02        ║ US      ║
║          5 ║ L03        ║ UK      ║
║          6 ║ L03        ║ US      ║
║          7 ║ L03        ║ US      ║
╚════════════╩════════════╩═════════╝

Required Output:

╔════════════╦════════════╦═════════╗
║ Product ID ║ Lot Number ║ Country ║
╠════════════╬════════════╬═════════╣
║          1 ║ L01        ║ US      ║
║          3 ║ L01        ║ UK      ║
║          5 ║ L03        ║ UK      ║
║          6 ║ L03        ║ US      ║
║          7 ║ L03        ║ US      ║
╚════════════╩════════════╩═════════╝

Sergey Kalinichenko · Accepted Answer · 2013-11-21 00:43:00Z

2

That one is relatively straightforward:

SELECT *
FROM MyTable t1
WHERE EXISTS (
    SELECT * FROM MyTable t2
    WHERE t1.LotNumber=t2.LotNumber AND t1.Country <> t2.Country
)

This is self-explanatory: you want all rows such that there's at least one other row with the same lot number, but a different country. Note that in order to express this in SQL using the same table twice in the same query you need to give your table an alias: in the query above, that's t1 and t2.

answered Nov 21, 2013 at 0:43

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CRK Over a year ago

hi @dasblinkenlight I am wondering why the below query is not working. Any idea?

SELECT productid, lotnumber, country      FROM MyProducts t1     WHERE t1.ProductID in(     SELECT ProductID FROM MyProducts t2     WHERE t1.LotNumber=t2.LotNumber AND t1.Country <> t2.Country     )

Sergey Kalinichenko Over a year ago

@Rams The logic connecting the table t1 from the outer query to the table t2 from the subquery is strange: I do not see what rows it should be returning, given that the constraints go both ways (subquery constraints the outer query by ID, and the outer query constraints the subquery by LotNumber and Country).

Collectives™ on Stack Overflow

Complex SQL Query in one table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related