CODEWITHSUNDEEP
sqlinner-join
Ali Sheikhpour
Ali Sheikhpour

Reputation: 11086

Sql query to select jobs where start and target city are in my city list

Every driver has a list of cities where he travel between them (Table "mycities").

I want to show him a list of jobs (from JOBS table) where start city and target city are in his list. I am confused how to join tables to check both start city and target city at same time:

If I use OR operator, the result does not cover both cities and if I use AND operator, the result would be empty:

select * from jobs inner join mycities on 
(mycities.city=jobs.startcity or mycities.city=jobs.targetcity)

Upvotes: 0

Views: 186

Answers (2)

Vether
Vether

Reputation: 65

(mycities.city=jobs.startcity or mycities.city=jobs.targetcity)

This means that startcity OR targetcity is in table "mycities".

(mycities.city=jobs.startcity and mycities.city=jobs.targetcity)

This means that startcity AND targetcity is in table "mycities", but

a=b and a=c mean also b=c

...so you will get only this jobs, where startcity = targetcity and that's why result is empty.

You probably want to use something like:

SELECT * FROM jobs WHERE 
   startcity IN (SELECT * FROM mycities) AND
   targetcity IN (SELECT * FROM mycities);

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

You'd join the city table twice, once for the start city once for the target one. Use table aliases to tell them apart.

select * 
from jobs 
inner join mycities start on start.city = jobs.startcity
inner join mycities target on target.city = jobs.targetcity;

And in case you don't want to select any data from the city table, you wouldn't join at all, but use IN or EXISTS instead:

select * 
from jobs 
where startcity in (select city from mycities)
and targetcity in (select city from mycities);

Upvotes: 1

Related Questions