Query MySql for User with Single Record

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
benxmy
Forum Newbie
Posts: 3
Joined: Fri Sep 15, 2006 10:43 am

Query MySql for User with Single Record

Post by benxmy »

Hi,
I'm trying to create a query of table of purchase records to return only users who have made a single purchase, so basically it would return one-time purchasers. Every purchaser is assigned a unique id number (userid), so i've been trying to come up with a quick and simple query that searches for unique userids in the sales_record table. I can put together a script to do it the hard way (ie: do an initial query of the userid table, then query the sales_record table for each userid and only print out the rows returned where mysql_num_rows == 1) but i was wondering if anyone knew of a more elegant/quick way to do it.

Thanks!
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Query MySql for User with Single Record

Post by s.dot »

Hmm..

Code: Select all

SELECT count(*) AS `numpurchased`, `user_id` FROM `sales_record_table` HAVING `numpurchased` = 1 GROUP BY `user_id`
How does that work?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Query MySql for User with Single Record

Post by Benjamin »

Without knowing the fields..

Code: Select all

 
SELECT
  m.first_name,
  m.last_name,
  m.user_id
FROM
  members m
WHERE
  (SELECT COUNT(*) FROM orders o WHERE m.user_id = o.user_id) = 1
ORDER BY
  last_name, first_name ASC
 
Post Reply