April 14, 2012 at 1:01 pm
if I have follwing code:
select I.OID, I.segment,,
MAX(CASE WHEN S.SUBSCRIPTION_TYPE = '5' THEN 'Y' ELSE 'N' END ) PB_SUBS,
max(case when S.SUBSCRIPTION_TYPE ='12' then 'Y' else 'N' end) DAILY_TASTE,
MAX(CASE WHEN S.SUBSCRIPTION_TYPE ='8' THEN 'Y' ELSE 'N' END) COOKING_FOR_TWO
FROM MAIL_ID i JOIN SUBSCRIPTION_FCT S
ON I.IDENTITY_ID = S.IDENTITY_ID
WHERE S.SITE_CODE ='PB'and S.SUBSCRIPTION_END_DATE is not null
group by I.oid_customer_dim, I.segment
My rows counts are different, not same as my joined table:mail_id . SO in reality they want to know what happened to all those customers who don't show up in this query.
If I remove my filter S.SUBSCRIPTION_END_DATE is not null then I get almost same numbers as my joined table: mail_id.
But the confusion is that I have to have that filter of end_date and they want me to put end_date in select statement as well to qc the data if all the customers who were missing in my previous query show up as null. but if I keep that end_date in select statement my rows are much more higher.
Please help!!Hope this is less confusing!! just want to know that why select statements differ my row numbers.
April 15, 2012 at 1:12 pm
I think the first thing to ask is are you using the correct join? do expect that all the records in the Mail table will have a corresponding row in the subcriptions table?
***The first step is always the hardest *******
April 16, 2012 at 6:31 am
SQL is simply a query language. It would give you all the results depending on the query you ask. So, it is upto you to ask the right query. If you add a Where clause to your query and still want all the rows....that's impossible.
What you can do here is leave out the "S.SUBSCRIPTION_END_DATE" condition in the Where clause because it would not show in the final result(as NULL or anyother thing) as it is not present in the Select list.
Even if you want to or somehow get the NULL values of "S.SUBSCRIPTION_END_DATE" in the result set then you can Select with the function isNULL as follows:
Hope this helps.
April 17, 2012 at 11:14 am
Ok, let's start with this... Which table has more records than the other? My "guess" (since you have not included DDL for the tables and sample data as described in my signature link) is that your subscription end date is null for those who have not ended, or are recurring or something... So you lose those records.
Remember, an inner join will join all records that match in both tables. It will not return records that exist in tableA that do not have a matching record in tableB. It will also not return rows that exist in tableB that do not exist in tableA.
So, for your own debugging... Use a left join instead of an inner join and add a where for tableA.somecolumn IS NULL and you will see all records in tableB that do not match. Then do the same for tableB.
Jared
CE - Microsoft
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply