Multi tables selection

[sern]

hi.
let say i have 3 tables. i want to select records from table1 which quantity is more than records' quantity either in table2 or table3.
Ex. 3 tables have the same key columns: Code.
if Code is in table2, then it will retrieve the data from table2. Else, it will retrieve data from table3.
is it possible to put all these in one query?

 

[JamesLean]

Not entirely sure what you're after but is this anywhere close?

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

--James

 

[billchris]

James,

Wouldn't your query return records where t1.qty > 0 even if there are no matching records in t2 or t3?

How about this:

SELECT t1.code, t1.qty
FROM t1
  LEFT JOIN t2 ON t1.code = t2.code
  LEFT JOIN t3 ON t1.code = t3.code
WHERE t1.qty > COALESCE(t2.qty, t3.qty, t1.qty + 1)

And that assumes that t1.qty is not null...I think.


“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal

 

[JamesLean]

Yes, you're right billchris. I just made that assumption as it wasn't specified!

If you only wanted rows from t1 where they had a match in either t2 or t3 then you could just use:

WHERE t1.qty > COALESCE(t2.qty, t3.qty)

--James