CODEWITHSUNDEEP
mysqlsql
od3n
od3n

Reputation: 314

Return row with value exist in both rows

+------+------+------------+------+
| id   | type| design| location   |
+------+------+------------+------+
|    1 | abc |    A  | Location X |
|    2 | abc |    A  | Location Y |
|    3 | def |    A  | Location X |
+------+------+------------+------+

I have table like above.

Let say i want to get type where exist in Location X AND Y. It should return row 1 and 2.

what sql should i use?

Upvotes: 2

Views: 51

Answers (3)

Frederic
Frederic

Reputation: 1028

if you just need to retrieve the type that exists in both you should do

    select [type] 
    from ur_table 
    where location = 'Location X'
    intersect 
    select [type] 
    from ur_table 
    where location = 'Location Y'

but if you realy need all data from the 2 rows.. try

    select id,[type],design, location 
    from ur_table L
    where L.[type] in (select [type] from ur_table where location = 'Location X'
                       intersect select [type] from ur_table where location = 'Location Y'
                       )

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

SELECT *
FROM table1
WHERE type IN(SELECT type
              FROM table1
              WHERE location IN('location X', 'location y')
              GROUP BY type
              HAVING COUNT(type) = 2
             );

SQL Fiddle Demo

This will give you:

| ID | TYPE | DESIGN |   LOCATION |
-----------------------------------
|  1 |  abc |      A | Location X |
|  2 |  abc |      A | Location Y |

Upvotes: 0

John Woo
John Woo

Reputation: 263933

SELECT type
FROM    tableName
WHERE   location IN ('location x','location y')
GROUP   BY type
HAVING  COUNT(*) = 2

or if you want to have all those values, use JOIn

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
          SELECT  type
          FROM    tableName
          WHERE   location IN ('location x','location y')
          GROUP   BY type
          HAVING  COUNT(*) = 2
        ) b ON a.type = b.type

SOURCE

Upvotes: 2

Related Questions