MySQL: Selecting all the rows which are related with a particular value from another row
I have the following mysql table. I have been trying to select all the rows which are related with the row that has B as value in the code column. The relation is based on the value of the column trans_id
id trans_id code amount side 1 1 A 200 left 2 1 B 200 right 3 2 J 100 right 4 2 C 100 right 5 2 B 200 left 6 3 A 630 right 7 3 K 630 left
My Expected Result:
id trans_id code amount side 1 1 A 200 left 3 2 J 100 right 4 2 C 100 right
Could you please tell me what should be the mysql query to achieve this?
The following query should return the results you want. This uses a select query to return results to the WHERE clause.
SELECT * FROM yourTable WHERE trans_id IN ( SELECT trans_id FROM yourTable WHERE code='B' ) AND code!='B'
Your question is unclear, but as far as I understand, you could use a self join like this:
select a.id, a.trans_id, a.code, a.amount, a.side from table as a inner join table as b on (a.id=b.trans_id and b.code='B');
This will return the row with table.id=2:
id trans_id code amount side 2 1 B 200 right
select t1.* from table_name t1 inner join table_name t2 on t1.trans_id = t2.trans_id where t2.code = 'B' and t2.code <> t1.code
If I'm understanding your problem correctly then a subquery would get you what you need.
SELECT * FROM yourTable WHERE id IN (SELECT trans_id FROM yourTable WHERE code='B')