Rafanake Rafanake - 7 months ago 14
SQL Question

MySQL selecting rows when a linked field matches

Im trying to build a query where i get all the variants where the content matches the search.
For example, based on the table below i want to get all variants that contains the content = "red"

-------------------------------------------
| ID | Product | Variant | Option | Content |
-------------------------------------------
| 1 | Shirt | 1 | size | S |
| 2 | Shirt | 1 | color | red |
| 3 | Shirt | 2 | size | M |
| 4 | Shirt | 2 | color | red |
| 5 | Shirt | 3 | size | L |
| 6 | Shirt | 3 | color | red |
| 7 | Shirt | 4 | size | M |
| 8 | Shirt | 4 | color | blue |
-------------------------------------------


I would like the results to be something like:

-------------------------------------------
| ID | Product | Variant | Option | Content |
-------------------------------------------
| 1 | Shirt | 1 | size | S |
| 2 | Shirt | 1 | color | red |
| 3 | Shirt | 2 | size | M |
| 4 | Shirt | 2 | color | red |
| 5 | Shirt | 3 | size | L |
| 6 | Shirt | 3 | color | red |
-------------------------------------------


And the result when the content = M, would be something like this

-------------------------------------------
| ID | Product | Variant | Option | Content |
-------------------------------------------
| 3 | Shirt | 2 | size | M |
| 4 | Shirt | 2 | color | red |
| 7 | Shirt | 4 | size | M |
| 8 | Shirt | 4 | color | blue |
-------------------------------------------


I hope this makes sense.
Thanks!

I made a fiddle
http://sqlfiddle.com/#!9/63e64e/2

Answer

Use a self-join

SELECT v1.*
FROM variant AS v1
JOIN variant AS v2 ON v1.variant = v2.variant
WHERE v2.content = 'red'

This is functionaly equivalent to Tin Tran's query, but doesn't require writing a subquery, so it's a little simpler. You'd need to check the EXPLAIN output to ensure that they're processed the same.