Reputation: 21
I'm having issue with a homework assignment where I have to use EXISTS to filter results where the date is 2016-07-23 but it always returns everything instead. I can get this to work using just inner joins so I must be using EXISTS improperly but I cant figure out where I'm going wrong. I'll include my code below, I'm working in a SQLquery database.
select
c.last_name, t.trip_name, t.start_location
from
customer c
inner join
reservation r on c.customer_num = r.customer_num
inner join
trip t on t.trip_ID = r.trip_id
where
exists (select r.trip_date
from reservation r
where r.trip_date = '2016-07-23');
Upvotes: 0
Views: 100
Reputation: 2312
You need to connect the sub-query in your exists statement to the outer query, otherwise everything will return because the exists statement will always evaluate to true:
select
c.last_name, t.trip_name, t.start_location
from
customer c
inner join
reservation r on c.customer_num = r.customer_num
inner join
trip t on t.trip_ID = r.trip_id
where
exists (select r2.trip_date
from reservation r2
where
r2.customer_num = c.customer_num --connect to current customer
and r2.trip_date = '2016-07-23');
This will give you all customers that have a reservation on 7/23/16. It's also possible that you might need to connect the sub-query to the trip table by adding this to the where clause:
and r2.trip_id = t.trip_id
This may be too narrow a filter though (can't say for sure as I don't know your data).
Upvotes: 1
Reputation: 950
As already pointed out you need to join to the outer table to filter records in the outer query.Your query should look like:
select c.last_name, t.trip_name, t.start_location
from customer c
inner join reservation r
on c.customer_num = r.customer_num
inner join trip t
on t.trip_ID = r.trip_id
where exists (
select ri.trip_date
from reservation ri
where r.customner_num = ri.customer_num
AND ri.trip_date = '2016-07-23');
Upvotes: 0
Reputation:
Your subquery results always with the same row, as someone pointed you should reference subquery to main query:
select c.last_name, t.trip_name, t.start_location
from customer c
inner join reservation r
on c.customer_num = r.customer_num
inner join trip t
on t.trip_ID = r.trip_id
where exists (
select ri.trip_date
from reservation ri
where ri.trip_date = r.trip_date);
I just guessed that you want to join reservation back to reservation in this awkward way. If you have other date field you want to join, just change it.
Upvotes: 0