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

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
yuviSELECT 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 <yuvalbra@.gmail.comwrote:

Quote:

Originally Posted by

>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

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

Quote:

Originally Posted by

>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

I need some help with update query

I have a select statement like select table1.field1, table2.field2, table 3.field3 from table1.inner join table1.field1 on table2.field2 some more joins inner join(derived table -) as newtable on some field.where ( this) and (this)I need this query to do the same thing, but I need to update the same tables and fields.I can find simple examples of inner joins but I cant piece together how to this.Can anyone just give me a basic outline of how an update would look?would I do likeupdate table1inner join table2.field1 on table1.field1 inner join table3 on table1then set table2.field1=@.var1... more sets

Ab:

You have "table1.field1, table2.field2, table3.field3" in your select list; you then say later, " ... I need to update the same tables and fields ..."

Understand, if you really are wanting to update multiple tables this cannot be done in a single update statement. Update statements can only operate on one table at a time. If you need to update three tables, you are going to need three separate updates -- each update statment corresponding to the particular table that is targeted for update. The update statements will tend to look something like

update table1
set field1 = newValue1,
field2 = newValue2,
...
fieldN = newValueN
from table1
someKindOfJoin table2
on (joinConditionsFor_2)
someKindOfJoin tableN
on (joinConditionsFor_N)
where (whereConditions)

|||I actually figured this out, but I guess there is no way to update multiple tables in 1 update query? Why can selects use multiple tables but updates cant?