joining 2 queries
-
I have 2 queries that work great separated but give me incorrect results when combined.
My first query is this:
SELECT TOP 100 PERCENT dbo.Reps.FULLNAME, COUNT(dbo.Accounts.[Last]) AS signedapps
FROM dbo.Reps INNER JOIN
dbo.Accounts ON dbo.Reps.ID = dbo.Accounts.Rep AND dbo.Reps.ID = dbo.Accounts.Rep AND dbo.Reps.ID = dbo.Accounts.Rep AND
dbo.Reps.ID = dbo.Accounts.Rep AND dbo.Reps.ID = dbo.Accounts.Rep
WHERE (dbo.Reps.DateTerminated IS NULL)
GROUP BY dbo.Reps.FULLNAME
as you can see it returns 2 fields. As does my second query shown below:
SELECT TOP 100 PERCENT dbo.Reps.FULLNAME, SUM(Marketing.dbo.SeminarSignups.BuyingUnits) AS buyingunits
FROM Marketing.dbo.Seminar_Agents INNER JOIN
Marketing.dbo.SeminarSignups ON Marketing.dbo.Seminar_Agents.gSID = Marketing.dbo.SeminarSignups.gSID INNER JOIN
dbo.Reps ON Marketing.dbo.Seminar_Agents.gAID = dbo.Reps.AGENTID
WHERE (dbo.Reps.DateTerminated IS NULL)
GROUP BY dbo.Reps.FULLNAME
ORDER BY dbo.Reps.FULLNAME
You will note that both queries use the Reps table, but when I try to simply combine the queries--
SELECT TOP 100 PERCENT dbo.Reps.FULLNAME, SUM(Marketing.dbo.SeminarSignups.BuyingUnits) AS buyingunits, COUNT(dbo.Accounts.[Last])
AS signedapps
FROM dbo.Reps INNER JOIN
Marketing.dbo.Seminar_Agents INNER JOIN
Marketing.dbo.SeminarSignups ON Marketing.dbo.Seminar_Agents.gSID = Marketing.dbo.SeminarSignups.gSID ON
dbo.Reps.AGENTID = Marketing.dbo.Seminar_Agents.gAID INNER JOIN
dbo.Accounts ON dbo.Reps.ID = dbo.Accounts.Rep
WHERE (dbo.Reps.DateTerminated IS NULL)
GROUP BY dbo.Reps.FULLNAME
ORDER BY dbo.Reps.FULLNAME
I can't seem to find out what the problem is here. I can feel that i'm close because some of my results for buying units is correct.
-
Thundr51
There may be an inadvertant typoe...try using abbreviations to make it easier to read.
SELECT TOP 100 PERCENT R.FULLNAME, SUM(S.BuyingUnits) AS buyingunits, COUNT(A.[Last])
AS signedapps
FROM dbo.Reps R
INNER JOIN Marketing.dbo.Seminar_Agents AG ON AG.gAID = R.AGENTID
INNER JOIN Marketing.dbo.SeminarSignups S ON S.gSID = AG.gSID
INNER JOIN dbo.Accounts A ON A.Rep = R.ID
WHERE (R.DateTerminated IS NULL)
GROUP BY R.FULLNAME
ORDER BY R.FULLNAME
I think there was a JOIN out of place.
Hope this works - I cannot test it.
Guarddata-
-
I'm afraid that didn't work. I'm not sure why but the results seem to be getting multiplied or something. Ex. I know that on my one of the records a rep has 77 buyingunits and 1 signedapp, but with my sql I get 77 buying units and 70 signedapps. Another ex: another rep has 13 signedapps but my results say 6059! I still haven't figured this one out.
-
Looks like your joins are ther problem
if you had rep 1 in reps
and 10 recs in accounts for that rep +
10 recs in seminars - you would get 1 * 10 * 10 records for that rep..
without changing your original sql - this is what i have
SELECT dbo.Reps.FullNAme,SignedApps.SignedApps,BuyingUnits.BuyingUnits
FROM dbo.Reps
INNER JOIN
(
SELECT dbo.Reps.Id, COUNT(dbo.Accounts.[Last]) AS signedapps
FROM dbo.Reps
INNER JOIN dbo.Accounts ON dbo.Reps.ID = dbo.Accounts.Rep
WHERE (dbo.Reps.DateTerminated IS NULL)
GROUP BY dbo.Reps.Id
) As SignedApps
ON dbo.Reps.Id = SignedApps.ID
INNER JOIN
(
SELECT dbo.Reps.Id, SUM(Marketing.dbo.SeminarSignups.BuyingUnits) AS buyingunits
FROM Marketing.dbo.Seminar_Agents
INNER JOIN Marketing.dbo.SeminarSignups ON Marketing.dbo.Seminar_Agents.gSID = Marketing.dbo.SeminarSignups.gSID
INNER JOIN dbo.Reps ON Marketing.dbo.Seminar_Agents.gAID = dbo.Reps.AGENTID
WHERE (dbo.Reps.DateTerminated IS NULL)
GROUP BY dbo.Reps.Id
) As BuyingUnits
ON dbo.Reps.Id = BuyingUnits.ID
-
In your third (combined) query, it doesn't look like you are JOINING correctly. You have 2 ON expressions one right after the other. Try this:
SELECT TOP 100 PERCENT dbo.Reps.FULLNAME, SUM(Marketing.dbo.SeminarSignups.BuyingUnits) AS buyingunits, COUNT(dbo.Accounts.[Last])
AS signedapps
FROM dbo.Reps
INNER JOIN Marketing.dbo.Seminar_Agents
ON dbo.Reps.AGENTID = Marketing.dbo.Seminar_Agents.gAID
INNER JOIN Marketing.dbo.SeminarSignups
ON Marketing.dbo.Seminar_Agents.gSID = Marketing.dbo.SeminarSignups.gSID
INNER JOIN dbo.Accounts
ON dbo.Reps.ID = dbo.Accounts.Rep
WHERE (dbo.Reps.DateTerminated IS NULL)
GROUP BY dbo.Reps.FULLNAME
ORDER BY dbo.Reps.FULLNAMEBTW, why the TOP 100 Percent? Oh, and I didn't know if it was just a typo (I assumed it was) but your first query has:
INNER JOIN
dbo.Accounts ON dbo.Reps.ID = dbo.Accounts.Rep AND dbo.Reps.ID = dbo.Accounts.Rep AND dbo.Reps.ID = dbo.Accounts.Rep AND
dbo.Reps.ID = dbo.Accounts.Rep AND dbo.Reps.ID = dbo.Accounts.Rep
That's a typo, right?
-
First off, i'd like to give a BIG thankyou to JRN
. His solution worked! I had no idea you could put a select in an inner join! (That's why I love posting here. I learn so much!)Secondly, Jpipes, yeah, it was a typo, normally, I use the querybuilder to make my queries but every once in a while it'll give me something like you saw. I thought I fixed it before I copied and pasted it but I must have been mistaken. Even so, with the typo out of the way, it still didn't work. I knew something was wrong with the join as some of the results were correct.
. His solution worked! I had no idea you could put a select in an inner join! (That's why I love posting here. I learn so much!)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply