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 |
CodePudding user response：
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;
CodePudding user response：
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);