CODEWITHSUNDEEP
mysqlsql
CRK
CRK

Reputation: 353

Complex SQL Query in one table

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      ║
╚════════════╩════════════╩═════════╝

Upvotes: 1

Views: 966

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726929

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.

Upvotes: 2

Related Questions