SQL select data from multiple tables with subqueries (including data from inner join) Error: 1242

Question

I want to display the ID from Table1 (TID) and the results of an inner join. The following statement is not working.

Situation: Two Tables:

• Table 1 PK:TID, FK: Table2_PID
• Table2 PK: PID, Name

Among other data I want to display the Name of every PID in Table1 which is stored in Table2.

SELECT T.TID 
    ,(Select P.Name
        from mydb.Table2 P
        inner join mydb.Table1 T
        on P.PID=T.Table2_PID) 
    FROM mydb.Ticket T;

Result: Error Code 1242. Subquery returns more than 1 row

I do know that the result returns more than 1 row, but I want to show the Name of every PID in Table1 which is stored in Table2. So any ideas on how I can do that?

PS: I'm using mySQL and working with MySQL Workbench v6.3

Answer 1

You don't need use the inner joins for getting all the names of the ID. You can try the default join to achieve the result.

select t2.pid, t2.name from mydb.Table2 t2, mydb.Table1 t1 where t1.pid = t2.pid;

Hope this helps.

Answer 2

You must use join like this

select t1.TID,t2.Name from Table1 t1 left join Table2 t2 on t1.Table2_PID=t2.PID

Answer 3

Thanks for the response, but the question/problem still remains. It wasn't about the join.

It is about the subquery and selecting multiple rows within it.

Answer 4

Thank you guys, I was thinking of a solution way to complicated. I resolved it using a simple where statement.

SELECT T.TID, P.Name
FROM mydb.table1 T, mydb.table2 P
WHERE P.PID=T.table2_PID;