CODEWITHSUNDEEP
mysqlsql
Bernard
Bernard

Reputation: 4590

How to have two nested queries inside one select statement?

I have three tables. One of the table is main table(TRIP). The other two tables are having foreign key to the main table. I want to query data which are in both tables and the condition on each table. Here is my code:

SELECT L#
FROM TRIP 
WHERE REG# IN 
(
    SELECT REG#
    FROM TRUCK
    WHERE REG# = 'PKR768'
)
AND T# IN
(
    SELECT T#
    FROM TRIPLEG
    WHERE TRIPLEG.DEPARTURE = 'Melbourne'
)

ERROR = missing right parenthesis

AFTER EDIT:

THERE IS ONLY ONE ROW RETURNED FROM TRUCK TABLE SO THE OUTPUT OF WHOLE SHOULD NOT BE MORE THAN 1 ROW BUT IT IS MORE THAN 1.

Upvotes: 0

Views: 51

Answers (2)

Gavin
Gavin

Reputation: 6480

Caveat I am not particularly familiar with mysql but I would express this query more naturally as

select tp.l#
from truck t
inner join trip tr on tr.reg# = t.reg#
inner join tripleg tl on tl.t# = tr.t# and tl.departure = 'Melbourne'
where t.reg# = 'PKR768'

Upvotes: 2

shree.pat18
shree.pat18

Reputation: 21757

You are missing a WHERE clause in the second query. Change it to:

SELECT T#
FROM TRIPLEG
WHERE TRIPLEG.DEPARTURE = 'Melbourne'

Upvotes: 1

Related Questions