Page 1 of 1

Data from multiple tables...

Posted: Sat May 31, 2008 9:20 pm
by jkashu
I am having a problem getting data from multiple tables:

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)
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????

Re: Data from multiple tables...

Posted: Sun Jun 01, 2008 4:20 am
by vargadanis
The way I usually get data from multiple tables is LEFT JOIN or RIGHT JOIN... They are useful...

Here is a link:
http://dev.mysql.com/doc/refman/5.1/en/join.html

Re: Data from multiple tables...

Posted: Sun Jun 01, 2008 4:15 pm
by califdon
jkashu wrote:I am having a problem getting data from multiple tables:

Code: Select all

...
                      SUM(signups.amount) AS amountever,
                      
                      COUNT(signupdate) AS allcount
                      FROM users WHERE EXISTS(SELECT userID FROM signups WHERE affiliateID = 1)
Specifically, it says it can't find table signups.... refering to signups.amount
You are telling MySQL that you want to refer to a field in a table named signups, but in your FROM clause, that is not a table that you refer to. The SQL parsing engine hasn't yet seen a reference to the table, even though you later refer to it in the WHERE clause. Try:

Code: Select all

FROM users, signups

Re: Data from multiple tables...

Posted: Sun Jun 01, 2008 5:48 pm
by Benjamin
I'll help you with this if you tell me what fields are in the users and signups tables.