How to retrieve rows dependent on reference table data in mysql?

user_17884919 0
Viewed 54

I have two tables like so..

Data Table

Key D1 D2
1 Blah Blah
2 Blah Blah
1 Blah Blah
3 Blah Blah
3 Blah Blah

Relation Table

id fkey status
1 2 1
2 1 3
3 2 1
1 3 1

If a user has an id=1. And for every id=1 in the reference table, as long as the status is equal to 1, I want to get the fkey (which references the Data Table key) and get all of the Data in the Data Table that matches that fkey. And I want to do that for every fkey in the Relation Table that has id=1.

For example, an end product would look like the following for id=1...

Final Table

key d1 d2
2 Blah Blah
3 Blah Blah
3 Blah Blah
mysql
2 Answers

Use a subquery to get all fkeys belonging to id 1 from table Relation, which then can be used as a match condition in table Data to get desired result.

select * from `Data` where `key` in
(select fkey from `Relation` where id=1 and `status`=1);
user_185543690

The expected output can be attained with the help of joins and subquery. I replicated the same table structure in my local and the below query worked for me.

SELECT `key`, `d1`, `d2` from tbl1 JOIN (SELECT * FROM tbl2 where status = 1 GROUP BY fkey) as finaltbl2 ON tbl1.key = finaltbl2.fkey;
user_124679930
Related