asked on

sql, inner join did not return right value

Hi Experts,

If I run
1) query A
select sum([Alternatedealmtd]) as AlternatedealMTD
,sum([ManualDealmtd]) as ManualDealMTD
,sum([wtd]) as WTD
,sum([mtd]) As MTD
,sum([lastmonth]) As LastMonth
,sum(lastmonthtotal) as LastMonthTotal
,case when sum(mtd)>0 then (cast(sum(MTD) as float)-cast(Sum(lastmonth)as float))/sum(lastmonth) *100
       else 0 end as Trending,
       count(distinct t.dealid)as testdrive
FROM [MMD_Feed].[dbo].[DART] AS dt
      inner JOIN Mmd_feed..deal d on dt.dealerid=d.dealerid
      inner JOIN [MakeMyDeal_com].[dbo].[TestDrive] t on t.dealid=d.dealid
where cast(d.submittedtimestamp as date)>=DateAdd("d",-(Day(getdate())-1), getdate()) and cast(d.submittedtimestamp as date)<getdate()

It returned me
AlternateDealMTD 165
ManualDealMTD 31
WTD 31
MTD 2105
LastMonth 1679
LastMonthTotal 2659
Treding 25
TestDrive 99

If I run
2) query B
SELECT sum([Alternatedealmtd]) as AlternatedealMTD
,sum([ManualDealmtd]) as ManualDealMTD
,sum([wtd]) as WTD
,sum([mtd]) As MTD
,sum([lastmonth]) As LastMonth
,sum(lastmonthtotal) as LastMonthTotal
,case when sum(mtd)>0 then (cast(sum(MTD) as float)-cast(Sum(lastmonth)as float))/sum(lastmonth) *100
       else 0 end as Trending
FROM [MMD_Feed].[dbo].[DART]

It returned me
AlternateDealMTD 87
ManualDealMTD 28
WTD 92
MTD 2532
LastMonth 1795
LastMonthTotal 2832
Treding 41

My question is query B has the correct result. But, I have add testdrive in the return value, but query A did not return me a correct result. I tried use left join, right join in query A and the result looks too ridiculous.

Please find attached sample data. Can u help on it? Thanks.

Sample.xlsx

dsacker

You do not have the same WHERE clause in query B. In fact, you don't have a WHERE clause at all in query B.

tanj1035

ASKER

I have to where clause in query A in order to get value of test drive. Since I have added the where clause in query A, the value of AlternateDealMTD,ManualDealMTD etc... became different from query B. Query B has the correct value for AlternateDealMTD,ManualDealMTD etc...

dsacker

Do you get the same results without the WHERE clause? If so, it may become necessary to SUM your totals in a subquery.

tanj1035

ASKER

If I add the where clause, the result is different. This is the problem. So, I do not know how to fix it. Can you show me the query? Thanks.

ASKER CERTIFIED SOLUTION

dsacker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tanj1035

ASKER

Thanks, it works! This is the new thing for me to learn!!!

dsacker

Glad that worked.

Be careful with separate queries in a sub-select. I intentionally did not join them, therefore they are called "cartesian" queries. If you leave off the WHERE clause that joins two queries like this, which returns detail records rather than SUMs, you can wind up with a runaway query.

Think of one entire table being fetched for every row in another entire table without condition. That's a cartesian query. It's worth reading about.

This was one of those rare times where it's justified.