need to know exist in one table and not exsit other

Y

yuvalbra

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
 
J

John Spencer

Try something like the following.

SELECT T1.ProdName
FROM TableOne as T1 LEFT JOIN TableTwo as T2
ON T1.ProdName = T2.ProdName
WHERE T2.ProdName is null
UNION
SELECT T1.ProdName
FROM TableOne as T1 LEFT JOIN TableTwo as T2
ON ("S" & T1.ProdName = T2.ProdName)
WHERE T2.ProdName is null

You _M_IG_H_T_ be able to do that with the following

SELECT T1.ProdName
FROM TableOne as T1 LEFT JOIN TableTwo as T2
ON (T1.ProdName = T2.ProdName or "S" & T1.ProdName = T2.ProdName)
WHERE T2.ProdName is null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Y

yuvalbra

Try something like the following.

SELECT T1.ProdName
FROM TableOne as T1 LEFT JOIN TableTwo as T2
ON T1.ProdName = T2.ProdName
WHERE T2.ProdName is null
UNION
SELECT T1.ProdName
FROM TableOne as T1 LEFT JOIN TableTwo as T2
ON ("S" & T1.ProdName = T2.ProdName)
WHERE T2.ProdName is null

You _M_IG_H_T_ be able to do that with the following

SELECT T1.ProdName
FROM TableOne as T1 LEFT JOIN TableTwo as T2
ON (T1.ProdName = T2.ProdName or "S" & T1.ProdName = T2.ProdName)
WHERE T2.ProdName is null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.










- Show quoted text -
Click to expand...

woooooooow it work fine
thanks thanks
 
Top