Return multiple rows from subquery

Question

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)

Racil Hilan · Accepted Answer · 2018-01-08 06:43:23Z

2

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.

edited Jan 8, 2018 at 6:43

answered Jan 8, 2018 at 6:34

Racil Hilan

25.4k13 gold badges56 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John James Over a year ago

I dont know if I am missing something but it still returns me the first row (from the join obv) only. I need the rows from the join to be added on the other selected rows only if they have the same filter1 (id) so I guess its a left join

Racil Hilan Over a year ago

We really need more details. Edit the question and add sample data and expected results.

Collectives™ on Stack Overflow

Return multiple rows from subquery

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related