Selecting values not in col2 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting values not in col2

Author

Topic

jughead1111
Starting Member

14 Posts

Posted - 2008-03-27 : 16:01:16

Hello,

I have a query that selects values based on a grouping. I need to select all rows where col1 is not in col2.

If the value is in col2 then don't include the row where it is in col1. In the below example I do not want the rows selected that are in bold.

I've tried for several days to get this but have been unable to do so. Could someone point me in right direction?

Sample Data -


Col1	 Col2	     Col3
G116519	 G116520     G116520-AM
G116519	 G116520     G116555
G116519	 G116531     G116531-AM
G116519	 G116531     G116555
G116519	 G116543     G116555
G116519	 G116543     G844218
G116519	 G116567     G844218
G116519	 G116579     G844218
G116519	 G843540     NULL
G116520	 G116520-AM  NULL
G116520	 G116555     NULL
G116531	 G116531-AM  NULL
G116531	 G116555     NULL
G116543	 G116555     NULL
G116543	 G844218     NULL
G116567	 G844218     NULL
G116579	 G844218     NULL
G116645	 G116646     G116646-AM
G116645	 G116658     NULL
G116645	 G116660     G855410
G116645	 G116683     NULL
G116646	 G116646-AM  NULL
G116660	 G855410     NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-27 : 16:09:42

SELECT * FROM Table WHERE PATINDEX('%'+Col1 + '%',Col2) =0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-27 : 16:25:42

Oh, come on Visakh! You can do better than that...


SELECT		t.*
FROM		Table1 AS t
LEFT JOIN	(
			SELECT		t1.Col1
			FROM		Table1 AS t1
			INNER JOIN	Table1 AS t2 ON t2.Col2 LIKE t1.Col1 + '%'
		) AS f ON f.Col1 = t.Col1
WHERE		f.Col1 IS NULL

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources