how to write this query - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

how to write this query

how to write this query# Database - 数据库

l*u2010-07-20 07:07

1 楼

table 1
product no. description
001 aa
002 bb
003 cc
table 2
Product no transcaction
001 111
001 222
003 rrr
how can I write query to find the product in table 1 that doesn't have any
transaction in table 2. query should return 2 by using the above data.

R*r2010-07-20 07:07

2 楼

SELECT t1.ProductNumber FROM
Table1 t1
LEFT JOIN Table2 t2 ON
t2.ProductNumber = t1.ProductNumber
WHERE t2.ProductNumber IS NULL

【在 l********u 的大作中提到】

: table 1
: product no. description
: 001 aa
: 002 bb
: 003 cc
: table 2
: Product no transcaction
: 001 111
: 001 222
: 003 rrr

l*u2010-07-20 07:07

3 楼

we need to put this query into a tool. however the tool has
"select * from table t1 where" hardcoded, left join is before where, so how
can I left join with query start with select * from where

B*g2010-07-20 07:07

4 楼

not exists

how

【在 l********u 的大作中提到】

: we need to put this query into a tool. however the tool has
: "select * from table t1 where" hardcoded, left join is before where, so how
: can I left join with query start with select * from where

l*u2010-07-20 07:07

5 楼

we need to put this query into a tool. however the tool has
"select * from table t1 where" hardcoded, left join is before where, so how
can I left join with query start with select * from where

l*u2010-07-20 07:07

6 楼

beijing code pls. thanks

【在 B*****g 的大作中提到】

: not exists
:
: how

B*g2010-07-20 07:07

7 楼

NOT EXSITS (SELECT 1 FROM table2 t2 WHERE t2.no = t.no)
or
no NOT IN (SELECT no FROM table2 t2)

【在 l********u 的大作中提到】

: beijing code pls. thanks