Welcome to WebmasterWorld Guest from 50.16.24.12

Forum Moderators: open

mySQL query for 3 tables

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

5+ Year Member



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

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

10+ Year Member



SELECT COUNT(a.adview_id)
FROM adviews a JOIN joblist j
ON a.adview_id = j.adview_id
WHERE j.advid = '1'
12:04 pm on Feb 3, 2010 (gmt 0)

5+ Year Member



thanks heaps, works now.
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month

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