Join issue - SQL Server Forums

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

Join issue

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-01-22 : 10:01:40

Hi All,

I have the following query. The issue is when I include Marketing_part_id is not null (which is the requirement) it is pulling results from 6/1/2007 to 8/1/08.
But when I exclude it ,it is showing me results from 11/106 to 1/1/09 (which is correct) but the count for Customer-id are not matching. I am not sure what could
be the issue.

SELECT
count (distinct(( orders.Customer_ID ))) AS Count,
sum(case when ( orders.Cancel_Date ) is not null then 1 else 0 end) AS Cancel,
convert (smalldatetime,
convert (varchar(2), month (orders.Order_Received_Date)) + '/1/' + convert (varchar(4), year (orders.Order_Received_Date)))
FROM
orders left join marketing
on marketing .order_id=orders.order_id
inner join mktg on mktg .marketing_id=orders.marketing_id

WHERE

orders.Billing_ID = 456
AND
((mktg.Type = 'Search' or mktg.Type = 'Leads' )
AND
(mktg.Type <> 'Search' or mktg.Type <> 'Leads'))
AND
mktg.New_Product = 'zone3'
AND
orders.Order_Received_Date >= '11/1/2006'
--AND marketing_partner_order.Marketing_part_ID is not null

Thanks for your help,
Petronas

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-01-22 : 10:11:54

By any chance, do you have NULL as a customer-id for some of the rows?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 10:38:08

that means excluded records have marketing_partner_order.Marketing_part_ID value as null. what count are yuou trying to find out? Also you're applying distinct so you will receive only count of distinct customers

Petronas
Posting Yak Master

134 Posts

Posted - 2009-01-22 : 10:43:57

Harsh/Visakh,

Thanks for your help. No, the customer_id does not have NUll in any rows. Also, I am trying to get a count of all the Cusitomer_id's .
If I get rid of the distinct. it shows me data from 10/1/07 to 07/1/08
which is incorrect. I appreciate your help

Thanks,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 10:48:14

what about values Marketing_part_ID & other columns like Billing_ID for excluded records?

Petronas
Posting Yak Master

134 Posts

Posted - 2009-01-22 : 11:00:15

Only marketing_partner_id has Null and Not Null values.

Thanks for your help,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 11:01:29

quote:
Originally posted by Petronas

Only marketing_partner_id has Null and Not Null values.

Thanks for your help,
Petronas

you mean for excluded ones marketing_partner_id has Null value?

Petronas
Posting Yak Master

134 Posts

Posted - 2009-01-22 : 11:27:23

Yes, the excluded ones have the Null values.and I do not want to count those,only that are not Null.

Thanks,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 11:32:03

then what's the problem? its working as expected, because of where condition marketing_partner_order.Marketing_part_ID is not null it will exclude them and count only the others

Petronas
Posting Yak Master

134 Posts

Posted - 2009-01-22 : 13:06:48

Thanks Visakh,

The issue is our Marketing guys are running thier own access query and their numbers and ours do not match.
Thanks for looking into the query, that confirms that our query is pulling the correct data. Appreciate your help and time.

Thanks again,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 13:07:47

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources