|
|
|
#1 |
|
Stupid Little Dreamer
Join Date: Aug 2000
Location: Toronto
Posts: 582
|
Can I do this in a single query?
At our website, registered users may choose to sign up for a season of premier membership. I'm trying to write a single query that will show some info about the user, how many premier seasons he or she has signed up for so far, and the date of the latest membership season. Something like, perhaps:
Code:
NAME.......................NUM SEASONS.................LATEST SEASON JOHN SMITH .................4 .........................SUMMER 2006 Suppose John Smith has signed up for Fall 2005, Winter 2006, Spring 2006 and Summer 2006, and that the seasons appear in the seasons table in that order. The following query will return John Smith, 4, Fall 2005: Code:
SELECT USR_username, COUNT(UAS_singleID) AS 'numSeasonsPlayed', SEA_longname FROM users JOIN users_and_memberships ON USR_userID = UAM_userID JOIN seasons ON UAM_seasonID = SEA_seasonID WHERE USR_userID = '100' GROUP BY USR_userID ORDER BY SEA_startdate DESC Code:
SELECT USR_username, SEA_longname FROM users JOIN users_and_memberships ON USR_userID = UAM_userID JOIN seasons ON UAM_seasonID = SEA_seasonID WHERE USR_userID = '100' ORDER BY SEA_startdate DESC LIMIT 1 Many thanks!
__________________
If it weren't for the last minute, nothing would get done ... Last edited by canuckster; 06-26-2006 at 10:59 AM. |
|
|
|
![]() |
|
||||||
| Thread Tools | Search this Thread |
| Display Modes | |
|
|