CODEWITHSUNDEEP
sqlexists
Andrew Wilson
Andrew Wilson

Reputation: 21

How to properly use EXISTS in SQL

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

Answers (3)

morgb
morgb

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

Amit Sukralia
Amit Sukralia

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

user170442
user170442

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

Related Questions