Stuck on a query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Stuck on a query

Author

Topic

AdamKosecki
Starting Member

18 Posts

Posted - 2006-01-26 : 20:31:32

Greetings,

I am attempting to get a list of customers who have referred more than 5 other customers that confirmed their email

Applicable column database schema:
customerid // customer's unique id
referrerid // the customerid of the customer that refered this customer. set to '' if was not referred
confirmemail // 0 if email was not confirmed, 1 if confirmed

I came up with this, but it returns no rows. Ah! I cannot figure out why. Any hints are appreciated.

SELECT c.customerid
FROM customer c
INNER JOIN customer x
ON x.referrerid = c.customerid
WHERE (
SELECT COUNT(*)
FROM customer d
WHERE d.customerid = c.customerid
) >= 5
AND x.referrerid <> ''
AND x.confirmemail = 1

It should be noted that removing the COUNT subquery from the WHERE clause gives me all the rows I would expect that query to: all the customers who refered someone else that is confirmed. I just can't figure out how to get the "5 or more" condition.

Thank you!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-26 : 20:46:34

This code is asking for the count of rows in table customer where customerid equals a single customerid in the outer customer table. Since customerid is the primary key, count(*) will always be one. The query is doing exacly what I would expect.


(
SELECT COUNT(*)
FROM customer d
WHERE d.customerid = c.customerid
)

This should do what you want:


SELECT
	c.customerid
FROM
	customer c
	INNER JOIN
	customer x
	ON x.referrerid = c.customerid
WHERE
	x.referrerid <> '' 
	AND x.confirmemail = 1
group by
	c.customerid
having
	count(*) >= 5

Edit: First!

CODO ERGO SUM

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-26 : 20:49:15

Your query could be fixed, but I'd rather show another method...


SELECT 		c.customerid, COUNT(*) As TotalReferrals
  FROM 		customer c
  INNER JOIN 	customer x
	ON 	x.referrerid = c.customerid
  WHERE 	x.referrerid <> '' 
  AND 		x.confirmemail = 1

  GROUP BY      c.customerid
  HAVING        COUNT(*) > 5

AdamKosecki
Starting Member

18 Posts

Posted - 2006-01-27 : 18:41:29

Ok, those queries work great! Thank you. I was not privy to the HAVING clause. I am now (I went on to read about it as well).

I see that the HAVING clause is the way to go, but a question remains just out of curriosity. In my original query I had a typo. It should have read this:

SELECT c.customerid
FROM customer c
INNER JOIN customer x
ON x.referrerid = c.customerid
WHERE (
SELECT COUNT(*)
FROM customer d
WHERE d.referrerid = c.customerid
) >= 5
AND x.referrerid <> ''
AND x.confirmemail = 1

Using that query it does not give me customers with more than 5 referals. I was assuming that would count how many records has the current customerid as the referrerid. Make sense? Clearly I'm wrong, but I don't know why :).

Thanks again!

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-27 : 22:03:47

Well..... you don't need the INNER JOIN


SELECT c.customerid
  FROM customer c
  WHERE (
    SELECT COUNT(*)
      FROM customer d
      WHERE d.referrerid = c.customerid AND d.confirmemail = 1) >= 5

-- WHERE x.referrerid <> '' isn't necessary - only values matching c.customerid count

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources