SQL Filter based on results from SQL query

Question

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

Get list of cities where both diesel and hybrid cars have either in start or end
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 ()

Gordon Linoff · Accepted Answer · 2016-10-21 02:43:06Z

1

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);

answered Oct 21, 2016 at 2:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Filter based on results from SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related