为何我写的sql query会重复同样的result两次？写错了吗？

为何以下的sql query会重复同样的result 两次?

SELECT customer.name, order.date FROM customer INNER JOIN order ON customer.CUSID = order.CUSID WHERE customer.CUSID = '123' AND customer.status = 'active'


以下是我的database structure:

customer table
record(1)
CUSID = [123]
name = [jesus]
status = [active]

record(2)
CUSID = [123]
name = [Micheal]
status = [active]

order table
record(1)
CUSID = [123]
date = [1-1-05]

record(2)
CUSID = [999]
date = [1-1-05]

record(3)
CUSID = [123]
date = [23-12-05]

[ 本帖最后由 kengkit 于 11-11-2005 01:26 AM 编辑 ]

显示出来的result如下:

Name      Date

Jesus     1-1-05
Jesus     1-1-05
Micheal   31-12-05
Micheal   31-12-05

为何这样呢？？

flashang

首先, 兩個 customer 的 cusid 都是 '123'

其次是需要用到 inner join 嗎? 還是 customer left join order ?

无论我用left join or right join..结果还是跟INNER JOIN一样！！

那么若原先条件不变，有可能只显示一个name吗？

[ 本帖最后由 kengkit 于 11-11-2005 02:31 AM 编辑 ]

trillian

customer table
record(1)
CUSID = [123]
name = [jesus]
status = [active]

record(2)
CUSID = [123]
name = [Micheal]
status = [active]

order table
record(1)
CUSID = [123]
date = [1-1-05]

record(2)
CUSID = [999]
date = [1-1-05]

record(3)
CUSID = [123]
date = [23-12-05]

不因该有两个custid=123.把custid 换成别的。。然后把 SELECT customer.name, order.date FROM customer INNER JOIN order ON customer.CUSID = order.CUSID WHERE customer.CUSID = '123' AND customer.status = 'active' 拿掉。

SELECT customer.name, order.date FROM customer INNER JOIN order ON customer.CUSID = order.CUSID WHERE customer.status = 'active'

shinelynn

你的CUSID应该是primary key来的..

chuanseng

SELECT customer.name, order.date FROM customer, order
WHERE customer.CUSID = order.CUSID
AND customer.CUSID = '123' AND customer.status = 'active'