CODEWITHSUNDEEP
mysqlsql
Thilo Savage
Thilo Savage

Reputation: 1071

MySQL query that matches two fields and has another field in common

I have an SQL table like this:

id |  f1 | f2
 1 |  a  | hi
 2 |  a  | sup
 3 |  b  | hi

I need a query that grabs the rows where f2 = hi OR f2 = sup, but only if f1 has the same value in both rows. So my query would grab id 1 and id 2, but NOT 3

So basically this -

SELECT * FROM `table` WHERE (`f2` = 'hi' OR `f2` = 'sup') AND 'f1-is-the-same'

Upvotes: 2

Views: 461

Answers (3)

Kibbee
Kibbee

Reputation: 66122

A query like this should do the trick

SELECT T1.id,T1.F1,T2.F2
FROM Table as T1
INNER JOIN Table as T2 
ON T1.F1=T2.F1 AND T1.ID <> T2.ID
WHERE T1.F2= 'hi' or T1.F1='sup'

Upvotes: 0

Jacob Schoen
Jacob Schoen

Reputation: 14202

SELECT * FROM `table` as t1 
WHERE (`f2` = 'hi' OR `f2` = 'sup') 
  AND f1 in (select f1 from `table` where id <> t1.id)

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

SELECT *
    FROM YourTable t1
        INNER JOIN YourTable t2
            ON t1.f1 = t2.f1
    WHERE t1.f2 = 'hi'
        AND t2.f2 = 'sup'

Upvotes: 2

Related Questions