SQL

[dev_rfid]

can i use a subquery field to my main query. i.e select x.field1,y.field2 from tbl1 as x where x.field3 not in (select y.field3 from tbl2 as y) :confused:

[Michael Potter]

Yes, do be aware that a 'not in' query can be very lengthy if your tables are large.

[dev_rfid]

thanks, than what can i do ? :confused:

[Michael Potter]

Sorry - didn't see your reference of y.field2 in the SELECT. Your original query will not work. You can try a left join:

SELECT 
    x.field1,
    y.field2 
FROM 
    tbl1 x
LEFT JOIN 
    tbl2 y
    ON (x.field3 = y.field3)
WHERE 
    y.field2 IS NULL

-- modified at 14:37 Friday 10th February, 2006

[try88]

try this,may work. select x.field1,y.field2 from tbl1 as x，tbl2 as y where x.field3 != y.field3 青山隐隐水迢迢，秋尽江南草未凋。二十四桥明月夜，玉人何处教吹箫。

[Ben Meyers]

The above will work, although you could also just do a normal inner join and use an inequality instead of the usual equality join, instead of the above example with a cartesian product and a where clause. SELECT x.field1, y.field2 FROM tbl1 as x INNER JOIN tbl2 as y ON x.field3 != y.field

[dev_rfid]

thanks, through it i solved my problem. thanks again.