The solution must work with SQL-92.

[cBeginner]

3 tables are in http://www.csdn.net/Expert/TopicView1.asp?id=951761

The output should be:

SupplierName CountOfOrdersWithLessThan70Items
Oracle 1
IBM 1
Informix 0
Microsoft 0

[Yang_]

SQL-92下工作，就是不能用join的说法是不对的,连接是最基本的数据库操作,SQL-92支持连接.

[cBeginner]

ok, thank you for your answer.

Can you do this question above?

[cBeginner]

谁知道http://www.csdn.net/Expert/TopicView1.asp?id=951761里的3个table，要怎么写SQL才能得出上面的结果？

[Yang_]

SELECT Supplier.SupplierName,Count(OrderItem.OrderItemID) AS CountOfOrdersWithLessThan70Items
FROM Supplier
LEFT JOIN Order
ON Supplier.SupplierID=Order.SupplierID and Order.SupplierID in (
SELECT SupplierID FROM OrderItem INNER JOIN Order
ON OrderItem.OrderID=Order.OrderID
GROUP BY Order.SupplierID
HAVING SUM(Requested)<70
)
left join OrderItem
ON OrderItem.OrderID=Order.OrderID

[cBeginner]

TABLENAME: Supplier
=========================
SupplierID SupplierName
1 IBM
2 Microsoft
3 Oracle
4 Informix

TABLENAME: Order
=========================
OrderID SupplierID
100001 2
100002 1
100003 3

TABLENAME: OrderItem
===============================
OrderItemID OrderID Requested
1 100001 30
2 100001 40
3 100002 20
4 100003 60


输出结果应该是:

SupplierName CountOfOrdersWithLessThan70Items
Oracle 1
IBM 1
Informix 0
Microsoft 0

=========================
我就是不知道怎么得出1和0

[Yang_]

SELECT SupplierName,ISNULL(CountOfOrders,0) AS CountOfOrdersWithLessThan70Items
FROM Supplier
LEFT JOIN
(
SELECT SupplierID,COUNT(*) AS CountOfOrders FROM OrderItem INNER JOIN Order
ON OrderItem.OrderID=Order.OrderID
GROUP BY Order.SupplierID
HAVING SUM(Requested)<70
) AS A

[Yang_]

SELECT SupplierName,ISNULL(CountOfOrders,0) AS CountOfOrdersWithLessThan70Items
FROM Supplier
LEFT JOIN
(
SELECT SupplierID,COUNT(*) AS CountOfOrders FROM OrderItem INNER JOIN Order
ON OrderItem.OrderID=Order.OrderID
GROUP BY Order.SupplierID
HAVING SUM(Requested)<70
) AS A
ON Supplier.SupplierID=A.SupplierID

[cBeginner]

有错，他说ISNULL(CountOfOrders,0)里的参数数目不对

[cBeginner]

这次可以了，但是输出要：
Oracle 1
IBM 1
Informix 0
Microsoft 0

我在最后加GROUP BY，他要我输入参数，应该怎么办？还有这个SQL可以在SQL-92下工作吗？能排序之后我就先把这个帖给结掉了。

[cBeginner]

[e-mail]这个地址不对，发不出去

[cBeginner]

收到了吗？

[Yang_]

收到了!
但是VBA我不是很熟，只回答了两个问题，回复了。

SELECT SupplierName, iif(CountOfOrders is null,0,CountOfOrders) AS CountOfOrdersWithLessThan70Items
FROM tblSupplier LEFT JOIN (SELECT SupplierID,COUNT(*) AS CountOfOrders FROM tblOrderItem INNER JOIN tblOrder
ON tblOrderItem.OrderID=tblOrder.OrderID
GROUP BY tblOrder.SupplierID
HAVING SUM(Requested)<70
) AS A ON tblSupplier.Supplierid=a.Supplierid
order by CountOfOrders desc;

[cBeginner]

输出要：
Oracle 1
IBM 1
Informix 0
Microsoft 0

我在最后加GROUP BY，他要我输入参数，应该怎么办？还有这个SQL可以在SQL-92下工作吗？

[cBeginner]

有报错，他说S

yntx error . in query expression ".

[cBeginner]

Syntx error . in query expression ".

[cBeginner]

我把数据库文件上传了，可以在里面试一下

www11.brinkster.com/karlcheng/a1.zip

[Yang_]

早说是ACCESS呀！

SELECT SupplierName, iif(CountOfOrders is null,0,CountOfOrders) AS CountOfOrdersWithLessThan70Items
FROM tblSupplier LEFT JOIN (SELECT SupplierID,COUNT(*) AS CountOfOrders FROM tblOrderItem INNER JOIN tblOrder
ON tblOrderItem.OrderID=tblOrder.OrderID
GROUP BY tblOrder.SupplierID
HAVING SUM(Requested)<70
) AS A ON tblSupplier.Supplierid=a.Supplierid;