jeudi 16 juin 2016

Get values for a reference where a key exists

I have a table that looks like this:

ref    key        val
--------------------------
1      Include    Yes
1      Color      Green
1      Shape      Square
2      Include    No
2      Color      Red
2      Shape      Circle

If an Include key exists with value Yes, I'd like to get all the values with the same ref.

So for the above example the result should be:

ref    key        val
--------------------------
1      Include    Yes
1      Color      Green
1      Shape      Square

This is what I have so far:

select *
from ref_table
where ref in
(
  select ref
  from ref_table
  where key = 'Include' and val = 'Yes'
)

This also seems to work:

with included
as
(
  select ref
  from ref_table
  where key = 'Include' and val = 'Yes'
)

select *
from ref_table
where ref in
(
  select * from included
)

Just wondering if there is a better (simpler) way to do this.

Aucun commentaire:

Enregistrer un commentaire