Data from multiple tables...

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
jkashu
Forum Commoner
Posts: 45
Joined: Tue Jan 30, 2007 12:00 pm

Data from multiple tables...

Post 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????
User avatar
vargadanis
Forum Contributor
Posts: 158
Joined: Sun Jun 01, 2008 3:48 am
Contact:

Re: Data from multiple tables...

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Data from multiple tables...

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Data from multiple tables...

Post by Benjamin »

I'll help you with this if you tell me what fields are in the users and signups tables.
Post Reply