How to have two nested queries inside one select statement?

Question

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.

Gavin · Accepted Answer · 2014-04-11 07:56:08Z

2

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'

edited Apr 11, 2014 at 7:56

answered Apr 11, 2014 at 7:42

Gavin

6,5603 gold badges28 silver badges25 bronze badges

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

1 Comment

Bernard Over a year ago

Thanks, it works fine but I wondered why if I add tl.departure = 'Melbourne' to the where condition it is not working. Any idea?

shree.pat18 · Accepted Answer · 2014-04-11 07:37:09Z

1

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

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

answered Apr 11, 2014 at 7:37

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

Collectives™ on Stack Overflow

How to have two nested queries inside one select statement?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related