-1

Trips

Customer_id   src_id  dest_id  
c1            1        2  
c1            2        1  
c2            1        2  
c2            2        3

Location

id name
1  Airports  
2  Movies  
3  Market

Is there any other good way than using EXCEPT

(select cust_id from trips t 
join location l
on src_id = id or dest_id=id
where name='Airport' ) 
except
(
select cust_id from trips t 
left join location l
on src_id =l.id 
left join location l2
on dest_id = l2.id 
where l.name not in('Airport') and l2.name not in('Airport')
  )

So the question was 1)Customers who used trips for Airports Follow up question : 2) Customers who used trips for only Airports?

For Question 1 Output would be c1 and c2 but for Question 2 it should be only c1 because c2 used trips for locations other than airports. I used EXCEPT but the interviewer mentioned I can some SQL function to remove c2 record and can be done without additional joins. I want to know if there is any function I am not aware of here.

More broad question: How to create queries to get ONLY records with conditions?

harshini
  • 77
  • 3
  • 7
  • 1
    Can you be a bit more clear on what exactly the end result should be given the sample data you provided? Should customers using airports in *either* the source or destination be included in the output set? Both source and destination? *Neither*? – esqew Nov 21 '20 at 05:40
  • Tag your question with the database you are using. Why doesn't the trips table have a `trip_id`? – Gordon Linoff Nov 21 '20 at 13:43
  • @esqew Yes , airport can be in either source or destination. – harshini Nov 21 '20 at 15:42
  • @harshini How have you identified that `c1` should be the output for question 2 (*only for airports*), if that customer also have a trip to destination `2 Movies` in the first row? What should be the output if there also was this routes: `(c1, 1, 2), (c1, 2, 3), (c1, 3, 1)`? How will you identify if that was a single trip or just a lot of trips and on one of them customer accidently visited the place where he or she started from on some date? – astentx Nov 21 '20 at 16:14
  • That is a good question! I'm confused by the actual question now. But as I understand , I think c1 is output there because 'Airport' is included in every trip of c1 whereas c2 has trips that doesn't have 'Airport' in either source or dest in atleast one of the trips. In the routes that you mentioned, then c1 will not be included because of (c1,2,3) record. – harshini Nov 21 '20 at 16:25

2 Answers2

2

You can try the below - DEMO Here

select customer_id
from trips t left join locations l
on src_id = l.id left join locations l1 on dest_id = l1.id
where l.name='Airports' or l1.name='Airports'
group by customer_id
having min(l.name)=min(l1.name) and max(l.name)=max(l1.name)
Fahmi
  • 37,315
  • 5
  • 22
  • 31
2

I would phrase this as a single join, then aggregation:

select t.customeri_d
from trips t
inner join location l on l.id in (t.src_id, t.dest_id)
group by t.customer_id
having min(l.name) = max(l.name) and min(l.name) = 'Airports'

You can also do this with two joins - but you need to put the filtering logic in the havingclause only, not in the where clause:

select t.customeri_d
from trips t
inner join location ls on l.id = t.src_id
inner join location ld on l.id = t.dest_id
group by t.customer_id
having min(ls.name) = max(ls.name) and min(ls.name) = 'Airports'
   and min(ld.name) = max(ld.name) and min(ld.name) = 'Airports'
GMB
  • 216,147
  • 25
  • 84
  • 135