Query Help - 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

Query Help

Author

Topic

Alina
Starting Member

20 Posts

Posted - 2007-11-11 : 14:59:40

Hello. I'm trying to run a query that will display my results for customers that made more than one purchase. So far I have the following query:

SELECT CUSTOMER.Name, CUSTOMER.City, CUSTOMER.StateProvince
FROM CUSTOMER, INVOICE
WHERE CUSTOMER.CustomerID=INVOICE.FK_CustomerID
AND INVOICE.FK_CustomerID IN
(SELECT count(INVOICE.FK_CustomerID) FROM INVOICE where FK_CustomerID > 1 group by FK_CustomerID)
ORDER BY CUSTOMER.StateProvince, CUSTOMER.City, CUSTOMER.Name

I know there's something wrong because I am getting zero rows for the results, but I don't know how to fix it. Can someone pleaes help me?
Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-11 : 16:16:15

quote:
Originally posted by Alina

SELECT CUSTOMER.Name, CUSTOMER.City, CUSTOMER.StateProvince
FROM CUSTOMER, INVOICE
WHERE CUSTOMER.CustomerID=INVOICE.FK_CustomerID
AND INVOICE.FK_CustomerID IN
(SELECT count(INVOICE.FK_CustomerID) FROM INVOICE where FK_CustomerID > 1 group by FK_CustomerID)
ORDER BY CUSTOMER.StateProvince, CUSTOMER.City, CUSTOMER.Name

SELECT c.Name, c.City, c.StateProvince
FROM CUSTOMER as c
WHERE c.CustomerID IN
(SELECT i.FK_CustomerID FROM INVOICE AS i where i.FK_CustomerID = c.CustomerID group by i.FK_CustomerID having count(*) > 1)
ORDER BY c.StateProvince, c.City, c.Name

E 12°55'05.25"
N 56°04'39.16"

Alina
Starting Member

20 Posts

Posted - 2007-11-12 : 01:08:29

Thank you Peso. That worked

Alina

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 03:37:20

This might perform better

SELECT		c.Name,
			c.City,
			c.StateProvince
FROM		CUSTOMER AS c
INNER JOIN	(
				SELECT		FK_CustomerID
				FROM		INVOICE
				GROUP BY	FK_CustomerID
				HAVING		COUNT(*) > 1
			) AS i ON FK_CustomerID = c.CustomerID
ORDER BY	c.StateProvince,
			c.City,
			c.Name

E 12°55'05.25"
N 56°04'39.16"

Alina
Starting Member

20 Posts

Posted - 2007-11-12 : 03:40:29

Thank you :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources