Data from multiple tables...
Posted: Sat May 31, 2008 9:20 pm
I am having a problem getting data from multiple tables:
Specifically, it says it can't find table signups.... refering to signups.amount
Also, does this work to find the number of signupdates in the user table where userIDs are equal to those that match the given afilliateID ( 1) in the signups table????
Code: Select all
SELECT
SUM(CASE WHEN signupdate = CURDATE() THEN 1 ELSE 0 END) AS numtoday,
SUM(CASE WHEN WEEK(signupdate) = WEEK(CURDATE()) THEN 1 ELSE 0 END) AS numthisweek,
SUM(CASE WHEN MONTH(signupdate) = MONTH(CURDATE()) THEN 1 ELSE 0 END) AS numthismonth,
SUM(CASE WHEN YEAR(signupdate) = YEAR(CURDATE()) THEN 1 ELSE 0 END) AS numthisyear,
SUM(CASE WHEN signupdate = CURDATE() THEN signups.amount ELSE 0 END) AS amounttoday,
SUM(CASE WHEN WEEK(signupdate) = WEEK(CURDATE()) THEN signups.amount ELSE 0 END) AS amountthisweek,
SUM(CASE WHEN MONTH(signupdate) = MONTH(CURDATE()) THEN signups.amount ELSE 0 END) AS amountthismonth,
SUM(CASE WHEN YEAR(signupdate) = YEAR(CURDATE()) THEN signups.amount ELSE 0 END) AS amountthisyear,
SUM(signups.amount) AS amountever,
COUNT(signupdate) AS allcount
FROM users WHERE EXISTS(SELECT userID FROM signups WHERE affiliateID = 1)Also, does this work to find the number of signupdates in the user table where userIDs are equal to those that match the given afilliateID ( 1) in the signups table????