CODEWITHSUNDEEP
mysql
Turab
Turab

Reputation: 191

How to select rows containing non unique keys using a unique key?

I have the following MySQL table;

----------------------------
| id |   pid   | operation |
----------------------------
| 5  |   12    |  pending  |
| 7  |   19    |  sent     |
| 8  |   12    |  replaced |
| 9  |   16    |  sent     |
| 12 |   12    |  closed   |
| 14 |   21    |  sent     |
----------------------------

id is a unique operation id, while pid is product id implying which operation is done for which product and therefore not unique. I need to list all operations of a product, using only an operation id.

I can do this by using a sub select like;

SELECT * FROM operations WHERE pid = (SELECT pid FROM operations WHERE id = 8);

This query lists the exact results I need, like;

----------------------------
| id |   pid   | operation |
----------------------------
| 5  |   12    |  pending  |
| 8  |   12    |  replaced |
| 12 |   12    |  closed   |
----------------------------

The question is; how can I do this without a sub select?

Upvotes: 1

Views: 121

Answers (2)

Raging Bull
Raging Bull

Reputation: 18747

Use JOIN:

SELECT o1.* 
FROM operations o1 join
     operations o2 on o1.pid=o2.pid
WHERE o2.id = 8

Result:

id  pid operation
-------------------
5   12  pending
8   12  replaced
12  12  closed

Sample result in SQL Fiddle

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

You can use join like this

SELECT a.* FROM operations a
join operations b on a.pid =b.pild
WHERE b.id=8

Upvotes: 1

Related Questions