How can I join these 3 tables
I have 3 tables:
Trip Promotion Promotion Cost.
1 ---- M 1 --------- M
Examples of data include:
TripID TripName Date
XYZ123 Hawaii 09/06/09
YTU574 Japan 09/09/09
GHR752 US 11/07/09
PromotionID TripID Name
1 XYZ123 Poster
2 XYZ123 Brochure
3 GHR752 TV ad
CostID PromotionID Cost
1 1 $50
2 1 $100
3 1 $120
4 3 $2000
5 2 $500
I am trying to build a query like this:
TripID Number of Promotions Total Cost
XYZ123 2 $770
GHR752 1 $2000
What I have is this:
SELECT
Trip.TripID, Count(Trip.TripID) AS [Number Of Promotions], Sum(PromotionCost.Cost) AS SumOfCost
FROM
Trip
INNER JOIN
(Promotion
INNER JOIN
PromotionCost ON Promotion.PromotionID = PromotionCost.PromotionID
) ON Trip.TripID = Promotion.TripID
GROUP BY
Trip.TripID;
And it gives me something like this:
TripID Number of Promotions Total Cost
XYZ123 4 $770
GHR752 1 $2000
I'm not sure why Number of promotions is so confusing for the first one (XYZ123). It seems that somehow JOIN is affecting this, because if I use this:
SELECT
Trip.TripID, Count(Trip.TripID) AS [Number Of Promotions],
FROM
Trip
INNER JOIN
Promotion ON Trip.TripID = Promotion.TripID
GROUP BY
Trip.TripID;
This gives me the correct number of promotions, which is only 2.
source to share
You can add the cost of each ad to a subquery. This way you only get one row per ad, and COUNT works to calculate the number of promotions per trip. For example:
select
t.TripId
, count(p.PromotionId) as [Number of Promotions]
, sum(pc.PromotionCost) as [Total Cost]
from trip t
left join promotions p on p.TripId = t.TripId
left join (
select
PromotionId
, PromotionCost = sum(cost)
from Promotions
group by PromotionId
) pc on pc.PromotionId = p.PromotionId
group by t.TripId
If MS Access doesn't allow subqueries, you can save the subquery in the view and join it.
source to share
You can try to compensate for duplicate promotion lines using COUNT(DISTINCT)
:
SELECT Trip.TripID, Count(DISTINCT Promotion.PromotionID) AS [Number Of Promotions],
Sum(PromotionCost.Cost) AS SumOfCost
FROM Trip INNER JOIN Promotion ON Trip.TripID = Promotion.TripID
INNER JOIN PromotionCost ON Promotion.PromotionID = PromotionCost.PromotionID
GROUP BY Trip.TripID;
What happens is that it defaults to COUNT()
counting rows created after all connections are made. There are four ad spend for TripID XYZ123, so four lines, although TripId occurs multiple times among those four lines.
It's easier to visualize if you try a similar query without GROUP BY:
SELECT Trip.TripID, Promotion.PromotionID, PromotionCost.Cost
FROM Trip INNER JOIN Promotion ON Trip.TripID = Promotion.TripID
INNER JOIN PromotionCost ON Promotion.PromotionID = PromotionCost.PromotionID;
You will see four lines for XYZ123 (with duplicate PromotionIDs) and one line for GHR752.
Re comments that MS Access doesn't support COUNT(DISTINCT)
: if that's the case then you shouldn't be doing it in one request. Do it with two queries:
SELECT Trip.TripID, SUM(PromotionCost.Cost) AS SumOfCost
FROM Trip INNER JOIN Promotion ON Trip.TripID = Promotion.TripID
INNER JOIN PromotionCost ON Promotion.PromotionID = PromotionCost.PromotionID
GROUP BY Trip.TripID;
SELECT Trip.TripID, Count(Promotion.PromotionID) AS [Number Of Promotions]
FROM Trip INNER JOIN Promotion ON Trip.TripID = Promotion.TripID
GROUP BY Trip.TripID;
The alternative is a very convoluted solution using subqueries described in this article at Microsoft:
http://blogs.msdn.com/access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx
source to share
Not an answer to your question, but a helpful tip (I hope): Convert your query to a view using the SQL Server Management Studio visual designer and examine the generated SQL. You don't need to actually store and use the generated view, but this is a good way to learn by example. I do this when I am struggling to complete a complex query.
EDIT . Shame on me, I read the tags: the question is related to MS-Access, not SQL Server. Anyway, I think my advice is still valid as conceptual learning is a problem as the SQL syntax is similar.
source to share