asked on

SQL, the table does not return value

Hi Expert,

If @Dealershipname = A which has 4 testdrive , the query will return the table with values.
If @Dealershipname = B which has 0 testdrive, the query will not return the values, only the row header.

Do you have any suggestions, thanks.

SELECT dt.[dealerid]
,dt.[name]
,dt.[Alternatedealmtd]
,dt.[ManualDealmtd]
,dt.[wtd]
,dt.[mtd]
,dt.[lastmonth]
,dt.lastmonthtotal
,dt.[trending]
,isnull(COUNT(t.dealid),0) as testdrive
FROM [MMD_Feed].[dbo].[DART] AS dt
INNER JOIN Mmd_feed..dealview AS dv
      on dt.dealerid=dv.dealerid
INNER JOIN Mmd_feed..deal d
      on dv.dealid=d.dealid
LEFT OUTER JOIN [MakeMyDeal_com].[dbo].[TestDrive] t
      on t.dealid=d.dealid
where name in (@DealershipName)
and t.dealid in (select t.dealid from [MakeMyDeal_com].[dbo].[TestDrive]
where cast(d.submittedtimestamp as date)>=DateAdd("d",-(Day(getdate())-1), getdate()) and cast(d.submittedtimestamp as date)<getdate())
group by dt.[dealerid]
,[name]
,[Alternatedealmtd]
,[ManualDealmtd]
,[wtd]
,[mtd]
,[lastmonth]
,lastmonthtotal
,[trending]

Vitor Montalvão

Since you are using LEFT JOIN (even I don't know where name is from which table) you can add:
(...) where (name in (@DealershipName) or name is null)
and (...)

Brian Crowe

Unclear on what your goal is.

If @Dealershipname = A which has 4 testdrive , the query will return the table with values.
If @Dealershipname = B which has 0 testdrive, the query will not return the values, only the row header.

Is this your goal or your current problem? If the former then what are you getting now, if latter then what are you trying to achieve?

Deepak Chauhan

this is because you are using AND condition here. Change AND to OR and see the dirrerence.

where name in (@DealershipName)
and t.dealid in (select t.dealid from [MakeMyDeal_com].[dbo].[TestDrive]

tanj1035

ASKER

hi Brain, sorry, this is the problem.
I want to get the table return value even dealershipename B has 0 test drive.

tanj1035

ASKER

Vitor and Deepak, thanks for your replies. Sorry, the queries do not work. It still cannot return value for the dealershipname B

Vitor Montalvão

Please provide some sample data so we can test before providing you an assertive answer.

tanj1035

ASKER

Sure, how you want me to provide the sample data? Thanks.

Vitor Montalvão

Few sample rows for each table. If the tables contains sensitive data don't forget to mask them.
Tables:
[MMD_Feed].[dbo].[DART]
Mmd_feed..dealview
Mmd_feed..deal
[MakeMyDeal_com].[dbo].[TestDrive]

tanj1035

ASKER

Vitor, please find attached excel, thx.
Sample.xlsx

ASKER CERTIFIED SOLUTION

Vitor Montalvão

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, Vitor. Yours worked out!!

I was originally trying to get the "isnull(COUNT(t.dealid),0) as testdrive" from "t.dealid in (select t.dealid from [MakeMyDeal_com].[dbo].[TestDrive] where cast(d.submittedtimestamp as date)>=DateAdd("d",-(Day(getdate())-1), getdate()) and cast(d.submittedtimestamp as date)<getdate())

It means the count has to be in [testdrive] from the date range from another table [deal].

Yours worked successfully without "t.dealid in (select t.dealid from [MakeMyDeal_com].[dbo].[TestDrive]" . So I do not need to especially to indicate dealid in [testdrive] in the query?

Vitor Montalvão

No, you don't because you already did in the JOIN clause:
LEFT OUTER JOIN [MakeMyDeal_com].[dbo].[TestDrive] t on t.dealid=d.dealid

tanj1035

ASKER

good point!!