Help with SELECT query with several JOINs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with SELECT query with several JOINs

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-06-25 : 12:42:18

Here's my schema:

-------------------------------------------------------
tblMembers
-------------------------------------------------------
MemberID | CountryID
-------------------------------------------------------

-------------------------------------------------------
tblCountries
-------------------------------------------------------
CountryID | CountryName
-------------------------------------------------------

-------------------------------------------------------
tblOrders
-------------------------------------------------------
OrderID | MemberID | OrderTypeID
-------------------------------------------------------

-------------------------------------------------------
tblSubscriptionOrders
-------------------------------------------------------
SubscriptionOrderID | OrderID | SubscriptionPackID
-------------------------------------------------------

-------------------------------------------------------
tblSubscriptionPacks
-------------------------------------------------------
SubscriptionPackID | TypeID
-------------------------------------------------------

Here's what I'm doing:
1. Output each country in one column
2. Output the number of subscriptions made from a member of that country where tblSubscriptionPacks.TypeID = 1 in the next column
3. Output the number of subscriptions made from a member of that country where tblSubscriptionPacks.TypeID = 2 in the next column

Here's my query:
SELECT c.CountryName,
SUM(CASE WHEN sp.TypeID=1 THEN 1 ELSE 0 END) AS Type1Count,
SUM(CASE WHEN sp.TypeID=2 THEN 1 ELSE 0 END) AS Type2Count
FROM tblOrders o
INNER JOIN tblMembers m
ON m.MemberID=o.MemberID
INNER JOIN tblCountries c
ON c.CountryID=m.CountryID
INNER JOIN tblSubscriptionOrders so
ON so.OrderID=o.OrderID
INNER JOIN tblSubscriptionPacks sp
ON sp.SubscriptionPackID=so.SubscriptionPackID
GROUP BY c.CountryName

But now I want to tweak something. In tblSubscriptionOrders, sometimes there are duplicate OrderID's. This isn't a bug, there are supposed to be duplicates sometimes, but I won't explain that. Right now, my query will count both OrderID's if there's a duplicate. I'd like to only count one if there's a duplicate.

How can I do this? All these joins are confusing me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 13:07:47

[code]SELECT c.CountryName,
SUM(CASE WHEN sp.TypeID=1 THEN 1 ELSE 0 END) AS Type1Count,
SUM(CASE WHEN sp.TypeID=2 THEN 1 ELSE 0 END) AS Type2Count
FROM tblOrders o
INNER JOIN tblMembers m
ON m.MemberID=o.MemberID
INNER JOIN tblCountries c
ON c.CountryID=m.CountryID
INNER JOIN (SELECT DISTINCT OrderID FROM tblSubscriptionOrders) so
ON so.OrderID=o.OrderID
INNER JOIN tblSubscriptionPacks sp
ON sp.SubscriptionPackID=so.SubscriptionPackID
GROUP BY c.CountryName[/code]

Apples
Posting Yak Master

146 Posts

Posted - 2008-06-25 : 14:02:17

quote:

Originally posted by visakh16

SELECT c.CountryName,
SUM(CASE WHEN sp.TypeID=1 THEN 1 ELSE 0 END) AS Type1Count,
SUM(CASE WHEN sp.TypeID=2 THEN 1 ELSE 0 END) AS Type2Count
FROM tblOrders o
INNER JOIN tblMembers m
ON m.MemberID=o.MemberID
INNER JOIN tblCountries c
ON c.CountryID=m.CountryID
INNER JOIN (SELECT DISTINCT OrderID FROM tblSubscriptionOrders) so
ON so.OrderID=o.OrderID
INNER JOIN tblSubscriptionPacks sp
ON sp.SubscriptionPackID=so.SubscriptionPackID
GROUP BY c.CountryName

That didn't quite work, I'm trying to get SubscriptionPackID out of tblSubscriptionOrders as well, so there's an error thrown saying that SubscriptionPackID is an invalid column name.

I tried (SELECT DISTINCT OrderID, SubscriptionPackID FROM tblSubscriptionOrders), but that didn't select distinct OrderID's

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 14:08:33

<<
I tried (SELECT DISTINCT OrderID, SubscriptionPackID FROM tblSubscriptionOrders), but that didn't select distinct OrderID's
>>

Not neccessarily
If you want to distinct OrderId, you have to pick either minimum or maximum of SubscriptionPackID

SELECT OrderID, Max(SubscriptionPackID) as SubscriptionPackID FROM tblSubscriptionOrders
GROUP BY OrderID

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources