June 20, 2008 at 8:54 am
SELECT Contact.Name, Contact.Phone, Contact.Email ,BundleList.Status as BundleStatus, ProductTypes.Producttype as ProductType,
ProductList.Status as ProductStatus, BundleType.Bundletype as BundleType, ProductList.Version
FROM
ProductTypes INNER JOIN
ProductList ON ProductTypes.id = ProductList.ProductType CROSS JOIN
Contact INNER JOIN
BundleList ON Contact.SiteId = BundleList.Siteid INNER JOIN
BundleType ON BundleList.BundleType = BundleType.id
WHERE (Contact.email = 'Unreg_user' or Contact.Email is null) and (BundleList.Status ='1' and ProductTypes.id ='1' and Bundletype.id ='1' and
ProductList.Version = '2003' or ProductList.Version = '2008')
I only get the values of "unreg_user" no nulls, i can get one or the other but not together?
I'm sure its wrong to do it like this or im missing something?
June 20, 2008 at 8:57 am
The code looks like it should work. I'd rewrite the joins and Where clause to the ANSI-92 standard, but that shouldn't make a difference in what you're trying to do here.
Have you tested to see if there are any rows that have null in that column, that also fulfill the rest of the Where clause requirements? It's possible none are being returned because there are none to return.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 9:01 am
If I take
(Contact.email = 'Unreg_user' or Contact.Email is null)
and seperate it out to...
(Contact.email = 'Unreg_user') I'll get about 3,000 that meet the where statement same if I put (Contact.Email is null) as long as they aren't together.
June 20, 2008 at 9:04 am
I guess I'd have to see the data to figure it out, in that case. It looks correct.
Can you modify the query to a union statement of the two separate queries?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 9:09 am
SELECT Contact.Name, Contact.Phone, Contact.Email ,BundleList.Status as BundleStatus, ProductTypes.Producttype as ProductType,
ProductList.Status as ProductStatus, BundleType.Bundletype as BundleType, ProductList.Version
FROM
ProductTypes INNER JOIN
ProductList ON ProductTypes.id = ProductList.ProductType CROSS JOIN
Contact INNER JOIN
BundleList ON Contact.SiteId = BundleList.Siteid INNER JOIN
BundleType ON BundleList.BundleType = BundleType.id
WHERE (Contact.email = 'Unreg_user' and BundleList.Status ='1' and ProductTypes.id ='1' and Bundletype.id ='1' and
ProductList.Version = '2003' or ProductList.Version = '2008')
or
(Contact.email is null and bundleList.Status = '1' and ProductTypes.id ='1' and Bundletype.id ='1' ) and
(ProductList.Version = '2003' or ProductList.Version = '2008')
Any idea why this would produce the required results?
June 20, 2008 at 11:55 am
I just noticed that almost your whole Where clause is predicated on that last "or". I think I was misreading that before. Are you certain that the last two tests aren't supposed to be isolated in their own parentheses? That changes the whole way the query works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 12:59 pm
rsheppick (6/20/2008)
SELECT Contact.Name, Contact.Phone, Contact.Email ,BundleList.Status as BundleStatus, ProductTypes.Producttype as ProductType,
ProductList.Status as ProductStatus, BundleType.Bundletype as BundleType, ProductList.Version
FROM
ProductTypes INNER JOIN
ProductList ON ProductTypes.id = ProductList.ProductType CROSS JOIN
Contact INNER JOIN
BundleList ON Contact.SiteId = BundleList.Siteid INNER JOIN
BundleType ON BundleList.BundleType = BundleType.id
WHERE (Contact.email = 'Unreg_user' and BundleList.Status ='1' and ProductTypes.id ='1' and Bundletype.id ='1' and
ProductList.Version = '2003' or ProductList.Version = '2008')
or
(Contact.email is null and bundleList.Status = '1' and ProductTypes.id ='1' and Bundletype.id ='1' ) and
(ProductList.Version = '2003' or ProductList.Version = '2008')
Any idea why this would produce the required results?
Yes, this is a different query from the original, and I don't think this is returning the results you think it is. I think what you want is this:
WHERE BundleList.Status = '1'
AND ProductTypes.id = '1'
AND Bundletype.id = '1'
AND ProductList.Version IN ('2003', '2008')
AND (Contact.Email = 'Unreg_user' OR Contact.Email IS NULL)
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2008 at 1:36 pm
Try this:
WHERE (Contact.email = 'Unreg_user' or Contact.Email = 'null' )
Manu
June 20, 2008 at 1:53 pm
Manu - unless you have specifically allowed for this, COLUMN = NULL will not work. You always should check for nulls using IS [NOT] NULL syntax.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2008 at 2:08 pm
hmm...looks to me that someone is storing 'null' as a string, and not NULL (the lack of value or the undefined state that is NULL).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply