CODEWITHSUNDEEP
mysqlsql
Adamo
Adamo

Reputation: 596

How to create query to avoid 'subquery returns more than 1 row'

That's my db schema

enter image description here

I need to view values from tab3 with order to sortId and id from tab2. So I create query:

SELECT * FROM test.tab3 where idTab2 = 1 and sortId = 1;

and it's ok.

Now I have a quest: id from tab1 and show values from tab3. Because tab1 and tab 2 have relation one to many, I do something like that:

SELECT * FROM test.tab3 where idTab2 = (select id from test.tab2 where idTab1 = 1);

but I expected the error: enter image description here

Now is my question. How to create query or maybe do other thing to create something like this pseudocode:

ids = select id from test.tab2 where idTab1 = 1 select * from tab.test3 where idTab2 = ids[0] and ids 1

Upvotes: 0

Views: 74

Answers (4)

Little Santi
Little Santi

Reputation: 8793

If I understood well, you want to get all the rows in tab3 related to tab1 through tab2, isn't it? Then, you need a join between the three tables:

SELECT tab3.* FROM tab3 
    INNER JOIN tab2 ON tab3.idTab2=tab2.id 
    INNER JOIN tab1 ON tab2.idTab1=tab1.id 
WHERE tab1.id=<your parameter>;

If you don't need to access any column in tab1 other than id, you could let tab1 appart of the join:

SELECT tab3.* FROM tab3 
    INNER JOIN tab2 ON tab3.idTab2=tab2.id 
WHERE tab2.idTab1=<your parameter>;

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726559

If the sub-select could return multiple rows, use EXISTS instead:

SELECT *
FROM test.tab3 t3
WHERE EXISTS (
    SELECT *
    FROM test.tab2 t2
    WHERE t2.idTab1 = 1 AND t3.idTab2 = t2.id
) --                    ^^^^^^^^^^^^^^^^^^^^^

The comparison of idTab2 = SELECT id ... has moved into the WHERE clause of the nested SELECT.

Upvotes: 2

frlan
frlan

Reputation: 7260

When using MySQL you can use the LIMIT-clause at the end of an (sub)query. This is limiting your result set to the number given. But be careful. As there might be no explicit order of results where the first in your case is chosen from, the results my not be deterministic.

Upvotes: 1

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

Use it as:

SELECT * FROM test.tab3 where idTab2 IN (select id from test.tab2 where idTab1 = 1);

To understand MySQL IN clause see it

Upvotes: 2

Related Questions