COUNT (3 Bảng) ???

[mykenty]

Câu lệnh mình như sau :

SELECT OA1.OffID FROM OfferApprover1 OA1
INNER JOIN Offer O ON O.OffID = OA1.OffID
WHERE EmpID = '20040707112408375' and Approved = 0

UNION

SELECT OA2.OffID FROM OfferApprover2 OA2
INNER JOIN Offer O ON O.OffID = OA2.OffID
WHERE EmpID = '20040707112408375' and Approved = 0

UNION

SELECT OAS.OffID FROM OfferApproverSpecial OAS
INNER JOIN Offer O ON O.OffID = OAS.OffID
WHERE EmpID = '20040707112408375' and Approved = 0

Kết quả ra là :
1. ***
2. ***
3. ***
4. ***
5. ***

Mình muốn đếm tổng số record của câu lệnh trên thì mình làm sao ? ( query ntn ?)
Thanks :helpsmili

[oj_n]

1. Union will return an unique OffID from each of the join statement (i.e. you will get 1 ***)
2. It's better to join all 4 tables together and find the count.

e.g.
SELECT count(*) as [total]
FROM Offer O join OfferApproverSpecial OAS on O.OffID = OAS.OffID
join OfferApprover1 OA1 on O.OffID = OA1.OffID
join OfferApprover2 OA2 on O.OffID = OA2.OffID
WHERE EmpID = '20040707112408375' and Approved = 0

The above assumes EmpID and Approved come from Offer table. If they're from OfferApprover(s) then...

SELECT count(*) as [total]
FROM Offer O join OfferApproverSpecial OAS on O.OffID = OAS.OffID
join OfferApprover1 OA1 on O.OffID = OA1.OffID
join OfferApprover2 OA2 on O.OffID = OA2.OffID
WHERE OAS.EmpID=OA1.EmpID and OAS.EmpID=OA2.EmpID
and OAS.Approved=OA1.Approved and OAS.Approved=OA2.Approved
and OAS.EmpID = '20040707112408375' and OAS.Approved = 0

[mykenty]

SELECT count(*) as [total]
FROM Offer O join OfferApproverSpecial OAS on O.OffID = OAS.OffID
join OfferApprover1 OA1 on O.OffID = OA1.OffID
join OfferApprover2 OA2 on O.OffID = OA2.OffID
WHERE EmpID = '20040707112408375' and Approved = 0

~> ko thực hiện được vì EmpID là của table OfferApproverSpecial, OfferApprover1, OfferApprover2


SELECT count(*) as [total]
FROM Offer O join OfferApproverSpecial OAS on O.OffID = OAS.OffID
join OfferApprover1 OA1 on O.OffID = OA1.OffID
join OfferApprover2 OA2 on O.OffID = OA2.OffID
WHERE OAS.EmpID=OA1.EmpID and OAS.EmpID=OA2.EmpID
and OAS.Approved=OA1.Approved and OAS.Approved=OA2.Approved
and OAS.EmpID = '20040707112408375' and OAS.Approved = 0

~> Kết quả là 0, vì table :

SELECT OA1.OffID FROM OfferApprover1 OA1
INNER JOIN Offer O ON O.OffID = OA1.OffID
WHERE EmpID = '20040707112408375' and Approved = 0
~> return row 3


SELECT OA2.OffID FROM OfferApprover2 OA2
INNER JOIN Offer O ON O.OffID = OA2.OffID
WHERE EmpID = '20040707112408375' and Approved = 0
~> return row 10


SELECT OAS.OffID FROM OfferApproverSpecial OAS
INNER JOIN Offer O ON O.OffID = OAS.OffID
WHERE EmpID = '20040707112408375' and Approved = 0
~> return row 0

Kết quả mình cần trả về là 10, vì Table OA2 có OffID trong OA1 và Talbe Offer O là tổng OffID.

Mình dùng cursor :

DECLARE X CURSOR FOR
SELECT OA1.OffID FROM OfferApprover1 OA1
INNER JOIN Offer O ON O.OffID = OA1.OffID
WHERE EmpID = '20040707112408375' and Approved = 0

UNION

SELECT OA2.OffID FROM OfferApprover2 OA2
INNER JOIN Offer O ON O.OffID = OA2.OffID
WHERE EmpID = '20040707112408375' and Approved = 0

UNION

SELECT OAS.OffID FROM OfferApproverSpecial OAS
INNER JOIN Offer O ON O.OffID = OAS.OffID
WHERE EmpID = '20040707112408375' and Approved = 0
OPEN X
FETCH NEXT FROM X
-- IF @@FETCH_STATUS = 0
SELECT @@CURSOR_ROWS AS ToTal
CLOSE X
DEALLOCATE X
~> 2 Kết quả là
1. OffID = 20040722083538875 // OffID của table đầu tiên OA1 ( mặc định )
2. Total = 10 ( kết quả mình cần tìm )

Vậy có cách nào chỉ ra một kết quả là Total ko ?
Thanks for answering :angel_not

[ICompare]

Đơn giản lắm, bạn thêm vào :
SELECT COUNT(OffID) As [Total] FROM
(
SELECT OA1.OffID FROM OfferApprover1 OA1
INNER JOIN Offer O ON O.OffID = OA1.OffID
WHERE EmpID = '20040707112408375' and Approved = 0

UNION

SELECT OA2.OffID FROM OfferApprover2 OA2
INNER JOIN Offer O ON O.OffID = OA2.OffID
WHERE EmpID = '20040707112408375' and Approved = 0

UNION

SELECT OAS.OffID FROM OfferApproverSpecial OAS
INNER JOIN Offer O ON O.OffID = OAS.OffID
WHERE EmpID = '20040707112408375' and Approved = 0
) AS T

Xong rồi đó nhóc, he he

[oj_n]

ICompare is correct - you can use a derived table and count. That would be faster than using a cursor. Especially, cursor will create locks which will degrade system performance. Also, consider using "union all" instead. This will avoid the unnecessary double filters (1=union , 2=count(OffID)).