Posted by Hugo Kornelis on 06/20/07 20:55

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

[Back to original message]