Re: trouble with exclusionary SQL query

In article <6abhs7$iub@cssun.mathcs.emory.edu>, Wanda Beck <wbeck@sybase.com> writes > >Hi, Everyone! > >I'm trying to formulate a query to select values from a column in one table >where the values do not appear in a column in a second table. I've tried >several different ways of querying and I either get all the rows from the >first table (even if they are in the second table) or no rows at all. > >TAB1.A is char(20) and TAB2.B is varchar(20). We're using ODS 7.23 on >HP-UX. > >I've tried the following: > >This got me everything in TAB1, even if it was in TAB2: >select distinct T1.A >from TAB1 as T1, TAB2 as T2 >where T1.A != T2.B Yes, pick one row in T1 call the value in column A for this row 'VALUEA' go through all the rows in b one the these rows in b will have T2 != 'VALUEA' For each row in A it is joined with ALL rows in B. > >This also got me everything in TAB1, even if it was in TAB2: >select distinct T1.A >from TAB1 as T1, outer TAB2 as T2 >where T1.A != T2.B> Same as above. >This got me 0 rows: >select A from TAB1 >where A not in( select B from TAB2 ) The means that all the values in TAB1.A exist somewhere in TAB2.B >I'd really appreciate your help in this because I'm stuck. Thanks! >-- >Wanda > > Can you give us some example data say TAB1 and TAB2 with 4 rows in each and what you want out of the query? A wild guess is gives that you need a common unique column in both A and B, all this column C. Then do select distinct T1.A from TAB1 as T1, TAB2 as T2 where T1.A != T2.B and T1.C = T2.C Or course you then need to think how NULLs in A and NULLS in B should be handled!! -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care