I need to find the rows that exist in one table but not in the other with condition

[yuval]

I need to find the rows that exist in one table but not in the other
with this condition:

(prod_name exist in table1 and not in table2.prod_name ) AND
(prod_name exist in table1 and not in table2.'S'+prod_name )
explanation:
i want to know if the product not exit and if the combination of the
charachter "S" with the product Name also not exist at the other
table
B.R
yuvi

[Roy Harvey]

SELECT prod_name
FROM table1 as A
WHERE NOT EXISTS
(select * from table2 as B
where A.prod_name = B.prod_name)
AND NOT EXISTS
(select * from table2 as C
where A.prod_name = 'S' + C.prod_name)

Roy Harvey
Beacon Falls, CT

On Wed, 20 Jun 2007 01:28:26 -0700, yuval <yu******@gmail.comwrote:

>I need to find the rows that exist in one table but not in the other
with this condition:

(prod_name exist in table1 and not in table2.prod_name ) AND
(prod_name exist in table1 and not in table2.'S'+prod_name )
explanation:
i want to know if the product not exit and if the combination of the
charachter "S" with the product Name also not exist at the other
table
B.R
yuvi

[Hugo Kornelis]

On Wed, 20 Jun 2007 01:28:26 -0700, yuval wrote:

>I need to find the rows that exist in one table but not in the other
with this condition:

(prod_name exist in table1 and not in table2.prod_name ) AND
(prod_name exist in table1 and not in table2.'S'+prod_name )
explanation:
i want to know if the product not exit and if the combination of the
charachter "S" with the product Name also not exist at the other
table

Hi yuvi,

Some alternatives for the solution posted by Roy. Try to see if they run
faster or slower on your system.

SELECT prod_name
FROM table1 AS a
WHERE NOT EXISTS
(SELECT *
FROM table2 AS b
WHERE a.prod_name IN (b.prod_name, 'S' + b.prod_name));

(Only SQL2005)

SELECT prod_name
FROM table1
EXCEPT
SELECT prod_name
FROM table2
EXCEPT
SELECT 'S' + prod_name
FROM table2;

SELECT prod_name
FROM table1
EXCEPT
(SELECT prod_name
FROM table2
UNION ALL
SELECT 'S' + prod_name
FROM table2);

(All the queries above are untested - see www.aspfaq.com/5006 if you
prefer a tested reply).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis