CODEWITHSUNDEEP
mysqlselectfield
Rafanake
Rafanake

Reputation: 77

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

Upvotes: 1

Views: 42

Answers (3)

DarkJade
DarkJade

Reputation: 270

SELECT * 
FROM variant
WHERE
variant.variant 
IN
(
  SELECT variant.variant 
  FROM variant 
  WHERE variant.content = 'red'
)

This should be relatively easy to understand. You have an inner query that first does a search for every variant with the option available. Then you have the outer query that searches for all values of all variants that were returned from the variant search. A more elegant way to write this could be something like,

SELECT *
FROM variant
INNER JOIN 
(
  SELECT variant2.variant 
  FROM variant variant2 
  WHERE variant2.content = 'red'
) variant2
ON variant.variant = variant2.variant

In this example, you would have to name at least one of the returned tables to differentiate them both. This is because you are calling two instances of the same table.

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

Here's another example that outputs the same result. The difference with this one is you are running it in a single query.

A good source to learn about joins from can be found here: https://technet.microsoft.com/en-us/library/ms191472(v=sql.105).aspx

One of my favorite diagrams on joins can be found here: http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg It's missing a few tricks, but it does give a good basic idea of what you can do with joins.

Upvotes: 1

Barmar
Barmar

Reputation: 781096

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.

Upvotes: 1

Tin Tran
Tin Tran

Reputation: 6202

SELECT v.id,v.product,v.variant,v.option,v.content
FROM variant v
INNER JOIN (SELECT variant FROM variant WHERE content = 'red')v2
ON v.variant = v2.variant
ORDER BY ID ASC

just change 'red' to 'M' if you want to get your other result.

http://sqlfiddle.com/#!9/63e64e/7

I noticed in your sqlfiddle it has a variant of M and Yellow that will show up in the result if you set the 'red' string to 'M'

Upvotes: 1

Related Questions