CODEWITHSUNDEEP
mysqlsql
user2775071
user2775071

Reputation: 11

Selecting rows that share a common value in one column, but were selected by other values

my example table:

CAR OPTIONS

| ID | CAR_ID |    DESCRIPTION |
|----|--------|----------------|
|  1 |      5 | tinted windows |
|  2 |      5 |  power windows |
|  3 |      6 |  power windows |
|  4 |      7 | tinted windows |

how do I write a sql statement that takes the inputs 'tinted windows' and 'power windows' and returns rows 1 and 2, (which share a common value in the car_id column) ?

Upvotes: 0

Views: 836

Answers (2)

peterm
peterm

Reputation: 92795

In this particular case you can do

SELECT * 
  FROM caroptions o JOIN
(
  SELECT car_id
    FROM caroptions
   WHERE description IN('tinted windows', 'power windows')
   GROUP BY car_id
  HAVING COUNT(DISTINCT description) = 2
) q  
    ON o.car_id = q.car_id

Output:

| ID | CAR_ID |    DESCRIPTION |
|----|--------|----------------|
|  1 |      5 | tinted windows |
|  2 |      5 |  power windows |

Here is SQLFiddle demo

Upvotes: 1

Praveen Lobo
Praveen Lobo

Reputation: 7187

Get all rows that match the inputs using WHERE, then GROUP BY the car_id HAVING at least one row for each of the input. You can use the query below to do that. 

SELECT car_id 
FROM CarOptions
WHERE (description = "tinted windows" OR 
       description = "power windows")
GROUP BY car_id
HAVING SUM(description = "tinted windows") > 0 AND 
       SUM(description = "power windows") > 0

See demo

To get entire row you can do,

SELECT *
FROM CarOptions
WHERE car_id IN (
  SELECT car_id
  FROM CarOptions
  WHERE (description = "tinted windows" OR
         description = "power windows")
  GROUP BY car_id
  HAVING SUM(description = "tinted windows") > 0 AND
         SUM(description = "power windows") > 0)

Upvotes: 1

Related Questions