CODEWITHSUNDEEP
mysqlsqlsql-server
Dan P.
Dan P.

Reputation: 1775

MySQL fetch data from another table where the column row matches a row result from first table

I have 2 tables, let's say they contain:

Table1

id | username

1  | Bla
4  | Bla2

Table2

FROM_ID | FK_TOID | Action

1       | 2       | -1
4       | 2       | -1

Now all I have is something like:

SELECT FK_FROMID FROM table2 WHERE FK_TOID = 2 AND FP_ACTION = -1

What I want to do is fetch the id and username from table1, where the id of table1 matches FROM_ID from table2, where FK_TOID is '2' in table2.

So basically the results returned should be something like

[{ id: 1, FK_FROMID: 1, username: Bla }, { id: 4, FK_FROMID: 4, username: Bla2 }]

Upvotes: 0

Views: 923

Answers (4)

jmail
jmail

Reputation: 6132

you need this:

SELECT A.id,B.from_id as FK_FROMID,A.username
FROM t1 A
LEFT JOIN t2 B
ON A.id=B.from_id

click this link to see the result:

http://sqlfiddle.com/#!2/868c1/4

update:1

    SELECT A.id,B.from_id as FK_FROMID,A.username
    FROM t1 A
    LEFT JOIN t2 B
    ON A.id=B.from_id 
    WHERE B.fk_toid=2 AND B.action=-1;

check this link:

http://sqlfiddle.com/#!2/868c1/8

Upvotes: 1

Stropharius
Stropharius

Reputation: 148

You should use the inner join or left outer join (does not matter for your example) as follows:

select t1.id, t2.FK_FROMID, t1.username 
from Table1 t1
join Table2 t2 
on (t2.FROM_ID = t1.id)
where t2.FK_VOID = 2 
and t2.FP_ACTION = -1

Upvotes: 0

Faran
Faran

Reputation: 71

You really don't need to use any joins for this. A simple query will work as follows:

select t1.id, t2.FK_FROMID, t1.username 
from Table1 t1, Table2 t2 
where t1.id = t2.FK_FROMID

Hope that helps!

Upvotes: 0

ScottMcGready
ScottMcGready

Reputation: 1622

try this:

SELECT * FROM table1 
LEFT JOIN table2 
ON table1.id = table2.from_id 
where table2.TOID = '2'

Upvotes: 0

Related Questions