CODEWITHSUNDEEP
sqlpostgresql
user3206440
user3206440

Reputation: 5059

SQL Filter based on results from SQL query

Input table - t1

make   | model | engine | kms_covered  | start   | end
-------------------------------------------------------
suzuki | sx4   | petrol | 11           | City A  | City D
suzuki | sx4   | diesel | 150          | City B  | City C
suzuki | swift | petrol | 140          | City C  | City B
suzuki | swift | diesel | 18           | City D  | City A
toyota | prius | petrol | 16           | City E  | City A
toyota | prius | hybrid | 250          | City B  | City E

Need to get a subset of the records such that start and end is only cities where both diesel and hybrid cars were either in start or end.

In above case, expect that only city B qualifies for the condition and expect output table as below

output table

make   | model | engine | kms_covered  | start   | end
-------------------------------------------------------
suzuki | sx4   | diesel | 150          | City B  | City C
suzuki | swift | petrol | 140          | City C  | City B
toyota | prius | hybrid | 250          | City B  | City E

Two step process

  1. Get list of cities where both diesel and hybrid cars have either in start or end
  2. Subset the table with only records having cities in #1

Need help with starting point as below.

select * from t1
 where start in () or end in ()

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

Hmmmm . . . If I understand the question, you can get the list of cities using a CTE and then use this in to solve your question:

with c as (
      select city
      from (select start as city, engine
            from t1
            union all
            select end, engine
            from t1
           )
      where engine in ('petrol', 'deisel')
      group by city
      having count(distinct engine) = 2
     )
select t1.*
from t1
where t1.start in (select city from c) and
      t1.end in (select city from c);

Upvotes: 1

Related Questions