Subsetting the original sql table using key pairs from a subset table

user_12549160 0
Viewed 31

I have the following table:

CREATE TABLE test (key1 varchar(10), key2 varchar(10), col3 varchar(10), 
                   col4 varchar(10), col5 varchar(10), val varchar(10))

INSERT INTO test VALUES('A', 'B', 'C', 'D', 'F', 'good1')
INSERT INTO test VALUES('F', 'C', 'C', 'D', 'F', 'bad1')
INSERT INTO test VALUES('A', 'D', 'C', 'D', 'F', 'good2')
INSERT INTO test VALUES('N', 'B', 'C', 'D', 'F', 'bad2')
INSERT INTO test VALUES('A', 'B', 'C', 'D', 'F', 'want this')
INSERT INTO test VALUES('A', 'D', 'C', 'D', 'F', 'and this')

I would like to extract key1, key2 pairs that where val column includes "good". Then I need to go back to the original table and extract all the entries where the mentioned key1, key2 pairs appear.

I am using the following:

select t.* from test t where exists (select 1
              from t t2
              where t2.value like '%good%') and
              t2.key1 = t.key1 and
              t2.key2 = t.key2)

Upon running the above, I get the following error:

"Object t does not exist"

mysql sql
1 Answers

You need to refer to the table as test, not t:

select t.*
from test t
where exists (select 1
              from test t2
              where t2.value like '%good%') and
                    t2.key1 = t.key1 and
                    t2.key2 = t.key2
              );
user_11440350
Related