Simple Select not returning any results

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

Simple Select not returning any results

Author

Topic

soulchyld21
Starting Member

28 Posts

Posted - 2012-02-08 : 10:52:03

Hi, I have come across an unusual error, The code below does not return any values, but if I select top 1000 rows from the Customer_Officer table I can see that both offers do exist and are indeed in the table, however they are in different rows (this should not make a difference? should it!)! Could it be a design flaw? I need a query that will return the customer number for a customer that has bought two different offers! Running the above query with only one condition, (Where) returns the correct results under either condition but not under both!

SELECT Customer_Number FROM Customer_Offer
WHERE Product_Offer = 'Offer2'
AND Product_Offer = 'Offer1'

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-08 : 11:04:23

HOW Can a row's column be the same exact value at the same time?

You want Product_Offer IN ('Offer1', 'Offer2')

OR

WHERE Product_Offer = 'Offer2'
OR Product_Offer = 'Offer1'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mhorseman
Starting Member

44 Posts

Posted - 2012-02-08 : 11:44:45

I think you're looking for Customers who have bought two offers

--Set up data
drop table #x
create table #x(customer_number int,product_offer varchar(10))

insert into #x
select 1,'Blah' union
select 1,'Offer1' union
select 2,'Offer1' union
select 3,'Offer1' union
select 3,'Offer2' union
select 4,'Offer2'

--Execute Query
select customer_number from #x

--where product_offer in ('Offer1','Offer2')

group by customer_number
having count(distinct product_offer) = 2

If you're specifically interested only in those who've bought "Offer1" and "Offer2", then uncomment the where clause,otherwise you'll get Customers who've bought any two products

Mark

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-08 : 11:59:31

OH, OK, you're suppose to READ these things..anothr way..the other one is better

SELECT Customer_Number FROM Customer_Offer
WHERE Product_Offer IN (SELECT Product_Offer FROM Customer_Offer = 'Offer2')
AND Product_Offer IN (SELECT Product_Offer FROM Customer_Offer = 'Offer1')

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

soulchyld21
Starting Member

28 Posts

Posted - 2012-02-08 : 17:06:09

Thanks, I also have to write a similar query that will return customers who have bought product_one and NOT bought product_2 or product_3, How would I construct that part of the query,
Many thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-08 : 19:05:21

Using Mark's test date and Brett's thoughts, you can construct the query sort of like this:

create table #x(customer_number int,product_offer varchar(10))

insert into #x
select 1,'Blah' union
select 1,'Offer1' union
select 2,'Offer1' union
select 3,'Offer1' union
select 3,'Offer2' union
select 4,'Offer2'

SELECT distinct customer_number
FROM #x x1
WHERE
	EXISTS 
		(SELECT * FROM #x x2 WHERE x2.customer_number = x1.customer_number 
			AND x2.product_offer = 'Offer1')
	AND NOT EXISTS
		(SELECT * FROM #x x3 WHERE x3.customer_number = x1.customer_number AND 
			x3.product_offer IN ( 'Offer2','Offer3') );
drop table #x

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-08 : 21:55:53

when is the homework due or when's the test?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

soulchyld21
Starting Member

28 Posts

Posted - 2012-02-09 : 05:51:21

Thanks Brett! Lol, My final year project is slightly more complicated,
Oracle and Application Express! arghhhhhhhh

Im getting this application coming along, its for someone to be able to analyse their customers! Can I pick your brain a little more?
We need to sum up their totals, I tried putting the sum function in my select query like this

SELECT Customer_Number, SUM(total) AS Total_Value FROM Customer_Offer
WHERE Product_Offer IN ('prod1', 'prod2')
AND Product_Offer NOT IN ('prod3','prod4')
GROUP BY Customer_Number

This works fine, however I need their totals for other products! say prod5, prod6 etc, (other products they may have bought), but my query only returns for prod1 and 2,

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-09 : 06:33:50

What virgin are you using?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

soulchyld21
Starting Member

28 Posts

Posted - 2012-02-09 : 06:46:37

For this project, SQL server 2008, for my uni work 11G

I Have removed the IN() part of the where clause in the above query and got the expected results however I am afraid I could end up with customers that have not bought product one and two (inevitably), (which I need to know) is there a way I can ensure those products have been purchased but not limit the rows for the sum() function?

soulchyld21
Starting Member

28 Posts

Posted - 2012-02-09 : 06:48:31

lol, assuming you were referring to software version above!

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-09 : 07:04:15

How about...



CREATE TABLE #Customer_Offer (Customer_Number int, Product_Offer varchar(10), Total money)
GO

INSERT INTO #Customer_Offer (Customer_Number, Product_Offer, Total)
SELECT 1,'Blah',   10.00 UNION ALL
SELECT 1,'Offer1', 20.00 UNION ALL -- 1 Won't Show up, Needs 1, 2, 5, Blah means nothing
SELECT 2,'Offer1', 30.00 UNION ALL -- 2 Only Has 1
SELECT 3,'Offer1', 40.00 UNION ALL -- 3 Will Show Up HAS 1,2,5 and No 3 and 4
SELECT 3,'Offer2', 50.00 UNION ALL
SELECT 3,'Offer5', 55.00 UNION ALL
SELECT 4,'Offer1', 60.00 UNION ALL -- 4 won't show up, has 1,2,5 AND a 4
SELECT 4,'Offer2', 40.00 UNION ALL
SELECT 4,'Offer5', 50.00 UNION ALL
SELECT 4,'Offer4', 60.00 UNION ALL
SELECT 5,'Offer1', 70.00 UNION ALL -- 5 won't show up, has 1,2,5 AND  3 & 4
SELECT 5,'Offer2', 80.00 UNION ALL
SELECT 5,'Offer5', 90.00 UNION ALL
SELECT 5,'Offer3', 90.99 UNION ALL
SELECT 5,'Offer4', 99.99
GO

    SELECT CO.Customer_Number
		 , SUM(Prod_Offer1_Total) AS SUM_Prod_Offer1_Total 
		 , SUM(Prod_Offer2_Total) AS SUM_Prod_Offer2_Total 
		 , SUM(Prod_Offer5_Total) AS SUM_Prod_Offer5_Total 
      FROM #Customer_Offer CO
      
INNER JOIN (SELECT Customer_Number, Product_Offer, Total AS Prod_Offer1_Total  
			  FROM #Customer_Offer WHERE Product_Offer = 'Offer1') AS O1
		ON CO.Customer_Number = O1.Customer_Number

INNER JOIN (SELECT Customer_Number, Product_Offer, Total AS Prod_Offer2_Total  
			  FROM #Customer_Offer WHERE Product_Offer = 'Offer2') AS O2
		ON CO.Customer_Number = O1.Customer_Number

INNER JOIN (SELECT Customer_Number, Product_Offer, Total AS Prod_Offer5_Total  
			  FROM #Customer_Offer WHERE Product_Offer = 'Offer5') AS O5
		ON CO.Customer_Number = O5.Customer_Number
		
	 WHERE NOT EXISTS (SELECT *
						 FROM #Customer_Offer N4
						WHERE Product_Offer = 'Offer4'
						  AND N4.Customer_Number = CO.Customer_Number)
	    OR NOT EXISTS (SELECT * 
						 FROM #Customer_Offer N5
						WHERE Product_Offer = 'Offer5'
						  AND N5.Customer_Number = CO.Customer_Number)
GROUP BY CO.Customer_Number
GO

DROP TABLE #Customer_Offer
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

soulchyld21
Starting Member

28 Posts

Posted - 2012-02-09 : 07:24:34

It was union I needed to use :-D, Thanks a million Brett,

Here's the query I wrote and it works, just testing it now. thanks again for all your time

SELECT Customer_Number, SUM(Price) As Total FROM Customer_Offer
WHERE Product_Offer NOT IN ('prod1')
GROUP BY Customer_Number
UNION ALL
SELECT Customer_Number, SUM(Price) As Total FROM Customer_Offer
WHERE Product_Offer IN ('prod2')
GROUP BY Customer_Number

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-09 : 07:33:03

If you say so....

That says, for a Customer_Number, Sum all of the totals

In 1 case SUM everything except Prod 1

In the other Sum Only Prod 2

So a). You are missing any sums for Prod 1, and b). You don't know which sums are what, and c). you're ignoring sums for a customer that has 1...and in the other customers that has more than Prod 2

You may get Customer number twice

Makes no sense to me

Cheers

soulchyld21
Starting Member

28 Posts

Posted - 2012-02-09 : 07:58:13

You are right, after sitting with a calculator I found some discrepancies,
I'm trying to get my head around it all lol, Guess the best way to learn is by making the mistakes!

soulchyld21
Starting Member

28 Posts

Posted - 2012-02-09 : 08:21:14

Ok, I have managed to cause abit of confusion, Yes I want to sum up totals for a customer and not for a product,
Im basically trying to work out the value a customer has! but need to filter this based on a product (I hope this makes sense) Im trying to say Give me the sum of all the purchases for a customer who has bought product a, but not bought product b and c or give me totals for a customer who has bought products b and d but not bought e.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-09 : 09:00:49

my advice...to mostly everyone....is to start simple...

Break up what you want to do into small pieces, and write that bit of code...then add on requirements one by one

Like

>> I want to sum up totals for a customer and not for a product

SELECT Customer_Num, SUM(Totals) AS SUM_Totals FROM Customer_Offer GROUP BY Customer_NUm

OK..that's the first part...

Now what's the next part of the problem?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

soulchyld21
Starting Member

28 Posts

Posted - 2012-02-09 : 09:12:00

The next part is I want the totals for customers who have bought certain products say the total for customers who have bought product a and not product b, however if they have bought product c, Id like that added to the total too but it must be mandatory that they have bought a and not bought b

soulchyld21
Starting Member

28 Posts

Posted - 2012-02-09 : 19:23:19

Finally can put this to bed! Thanks to all of you wonderful people, Got it working using UNION ALL Whoop Whoop!! :-D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources