CODEWITHSUNDEEP
mysql
black_belt
black_belt

Reputation: 6799

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?

Thanks :)

Upvotes: 1

Views: 2003

Answers (4)

Zagor23
Zagor23

Reputation: 1963

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'

Upvotes: 2

Chris
Chris

Reputation: 28064

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

Upvotes: 1

Radu M.
Radu M.

Reputation: 5738

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

Upvotes: 1

Dan Smith
Dan Smith

Reputation: 5685

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')

Upvotes: 0

Related Questions