I'm trying to do return results from one table based on multiple results on another. Here's the setup:
Table A: "accounts"
id | fname | other_id
1 | test | 500
2 | test2 | 505
3 | test3 | 500
4 | test4 | 540
5 | test5 | 500
Table B: "transactions"
id | account_id |
1 | 1
2 | 4
3 | 2
4 | 1
5 | 3
6 | 2
What I'm trying to accomplish is, return all id's from transactions where account_id = the id in table A WHERE other_id = a certain value.
To do write it out manually it would look like this:
So for example if other_id = 500.
1) get records from accounts where other_id = 500 (will be multiple results, in this case, 1, 3 and 5)
2) get records from transactions where account_id = 1 OR account_id = 3 OR account_id = 5
I've tried a few different subselects but can't seem to come up with what I'm looking for.
I of course could just break this up into a loop using PHP but I'd rather use a single query for efficiency.
No subselect required, just a simple join.
select * from accounts a, transactions t where t.account_id=a.id and other_id=500
select t.id
from accounts as a
inner join transactions as t on a.id = t.account_id
where a.other_id=500
If i understand you right, you want this.
SELECT b.id
FROM accounts AS a
LEFT JOIN transactions AS b ON b.account_id = a.id
WHERE other_id = 500