Subquery as a list of values

[jisoo23]

Hello all,

I'm wondering if this is the right way to do this...

I have two tables to query, one query will be dependent on another. For instance, I'm selecting a couple fields from table1 where their names (i.e. field1 contains 'this_name', 'first_name', 'second_name') will pop up if their partial names are not in the second table (called table2 with field2, which holds 'first', 'second'). I know how to exclude single results via this query:

select field1 from table1 where field1 not like 'first%';

This would return 'this_name' and 'second_name'. But how do I exclude based on the second table? This would be much more flexible since values in the second table to check for would change a lot. I was thinking something like this:

select field1 from table1 where field1 not like in '(select field2 from table2)%';

I know that statement isn't correct, can someone tell me how to correctly implement this? In this case, only 'this_name' would be returned.

Thanks,
Jisoo23

 

[Taris]

Try it this way:

select field1 from table1 where field1 not in '(select field2 from table2 where field2 like 'something')%';

Be carful with the IN-Clause. Postgres is not very fast with IN-Subselects with Version 7.3 and older versions

 

[jisoo23]

Thanks! I'm using 7.4 so I think I'll be ok. I'll let you know how it works out =)

Jisoo23

 

[jisoo23]

After taking a second look, I think the statement you gave me is slightly incorrect. The second table has the set of conditions I want to use for the first table. So the 'like' function should be used for the first table, not the second. Or am I getting the wrong idea?

Thanks!
Jisoo23

 

[Taris]

Maybe you should post the statement you have problems with. I have problems in understanding these example-statements with 'table1' etc.