CODEWITHSUNDEEP
mysqlselect
Rafanake
Rafanake

Reputation: 67

MySql - Selecting all records with the same field when one row matches the result

I have made a fiddle : http://sqlfiddle.com/#!9/b1f15

I Have this table

id |  sid  | product
---------------------
1  |  ABC  |   1
2  |  ABC  |   2
3  |  ABC  |   3
4  |  BCD  |   1
5  |  BCD  |   2
6  |  XYZ  |   2
7  |  XYZ  |   3

What i want is to get all SID's if one SID have the product = 3 The result i was looking is:

id |  sid  | product
---------------------
1  |  ABC  |   1
2  |  ABC  |   2
3  |  ABC  |   3
6  |  XYZ  |   2
7  |  XYZ  |   3

Im completely stuck, please help! Thanks

Upvotes: 0

Views: 63

Answers (5)

Vinay Prajapati
Vinay Prajapati

Reputation: 7504

Use simple left join as below:

select sp1.* from sp_test sp1 left join sp_test sp2 on sp1.sid=sp2.sid
where sp2.product=3;

Upvotes: 2

Mursaleen Ahmad
Mursaleen Ahmad

Reputation: 313

Use a simple nested query.

SELECT * FROM sp_test
WHERE sid IN (SELECT sid FROM sp_test WHERE product = 3);

Upvotes: 1

mechanical_meat
mechanical_meat

Reputation: 169274

Another variation:

select 
  id, sid, product
from 
  sp_test x
where 
  exists (
    select 
      1
    from 
      sp_test y
    where 
      y.product = '3' 
      and x.sid = y.sid)

Upvotes: 1

Blank
Blank

Reputation: 12378

Use self-join:

SELECT t1.*
FROM sp_test t1
JOIN sp_test t2
ON t1.sid = t2.sid AND t2.product = '3';

Or EXISTS:

SELECT *
FROM sp_test t1
WHERE EXISTS (
    SELECT  1 FROM sp_test t2 WHERE t1.sid = t2.sid AND t2.product = '3'
);

SQLFiddle Demo

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Use conditional aggregation:

SELECT t1.id, t1.sid, t1.product
FROM sp_test t1
INNER JOIN
(
    SELECT sid
    FROM sp_test
    GROUP BY sid
    HAVING SUM(CASE WHEN product = 3 THEN 1 ELSE 0 END) > 0
) t2
    ON t1.sid = t2.sid

Follow the link below for a running demo:

SQLFiddle

Upvotes: 0

Related Questions