homepage Welcome to WebmasterWorld Guest from 54.166.10.100
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
mySQL query for 3 tables
tommy98




msg:4071434
 12:49 pm on Jan 31, 2010 (gmt 0)

I want to write an SQL statement that counts the number of adviews where advertiser = 1

SELECT COUNT(a.adview_id) FROM {adviews} a JOIN {joblist} j
WHERE j.advid = '1'

But that doesnt seem to work. What is the best way to do this?

CREATE TABLE IF NOT EXISTS {joblist} (
jobid INT(10) NOT NULL AUTO_INCREMENT,
advid INT(10) NOT NULL DEFAULT 0,
PRIMARY KEY jobid (jobid))

CREATE TABLE IF NOT EXISTS {advertisers} (
advid INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY advid (advid))

CREATE TABLE IF NOT EXISTS {adviews} (
adview_id BIGINT NOT NULL AUTO_INCREMENT,
jobid INT(10) NOT NULL,
referer VARCHAR(250) NOT NULL DEFAULT '',
browser_ip VARCHAR(15) NOT NULL DEFAULT '',
datecreated INT(11) NOT NULL DEFAULT 0,
uri VARCHAR(250) NOT NULL DEFAULT '',
username VARCHAR(96) NOT NULL DEFAULT '',
PRIMARY KEY (adview_id)}

 

syber




msg:4071565
 7:44 pm on Jan 31, 2010 (gmt 0)

SELECT COUNT(a.adview_id)
FROM adviews a JOIN joblist j
ON a.adview_id = j.adview_id
WHERE j.advid = '1'

tommy98




msg:4073401
 12:04 pm on Feb 3, 2010 (gmt 0)

thanks heaps, works now.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved