Join tables 1

[sern]

Hi.
I have 3 tables in SQL: T1, T2, T3.
I wish to display data from T1 where the quantity is more than quantity in T2 but not in T3.
I used below query but it doesn't return any result.

SELECT Code, Desc
FROM T1 LEFT JOIN T2
ON T1.Code = T2.Code
WHERE T1.Qty > T2.Qty AND T1.Code NOT IN
(SELECT Code FROM T3)

Anyone pls help on this..
Thanks..

 

[plantj]

I'm surprised if even runs: Code is an ambiguous fieldname (contained in T1 and T2).

This aside your left join will work as an inner join since you reference T2 in the where clause.

Try something like...

SELECT T1.Code, T1.Desc
FROM T1 LEFT JOIN T2
ON T1.Code = T2.Code
and T1.Qty > T2.Qty
where T1.Code NOT IN
(SELECT Code FROM T3)

The condition off qty>qty is a bit of a worry also. Is is possible to have multiple T1's with same code and does it matter that you'll get duplicates?

jp

 

[sern]

thanks plantj.
now i can get the data already.

 

[sern]

Oops...
I think I have done a mistake.
This query only show the those Codes which inside T2. Those inside T1 but not in T2 not appear.
So, do you have any ideas??

 

[JamesLean]

Try this:

SELECT t1.code, t1.desc
FROM t1 LEFT JOIN t2 ON t1.code = t2.code
WHERE t1.qty > COALESCE(t2.qty, 0)
  AND NOT EXISTS (
    SELECT code FROM t3
    WHERE code = t1.code
  )

--James

 

[sern]

ok. now the query run properly. thanks all.