CODEWITHSUNDEEP
mysqlselectjoinrows
John James
John James

Reputation: 657

Return multiple rows from subquery

Basically I have two tables. 1 and 2. I need the field2 column in the table2 table to return multiple rows. I tried the below join (simplified the columns) but unfortunately it returns me only one result.

SELECT table1.field1, table1.field2, table1.field3, sub_q.field4
FROM table1
JOIN (
    SELECT t2.field4, t2.filter1, t2.filter2 FROM table2 t2
) sub_q ON (sub_q.filter1 = table1.id AND sub_q.filter2 = 1) 
##Should return multiple rows
##but returns only one!
WHERE table1.id = ..;

Edit:

I created a schema here: http://sqlfiddle.com/#!9/1c5737 with the select query as

SELECT t1.field1, t1.field2, t1.field3, t2.field1
FROM table1 t1
JOIN table2 t2 ON t2.filter1 = t1.id AND t2.filter2 = 1
WHERE t1.id = 1;

Only to find out that it works there, so I come back in shame to accept the answer and check where I messed up in my query (probably one of the fields)

Upvotes: 0

Views: 146

Answers (1)

Racil Hilan
Racil Hilan

Reputation: 25351

Why are you using a subquery in the join? This is how it should be written:

SELECT table1.field1, table1.field2, table1.field3, t2.field1
FROM table1 t1
JOIN table2 t2 ON t2.filter1 = table1.id AND t2.filter2 = 1

Also it is likely that you need LEFT JOIN (or INNER JOIN) instead of JOIN, but cannot be sure without more details on what you're trying to achieve.

Upvotes: 2

Related Questions