Hi,
I am wondering if it is possible to link or join three tables together?
they are all linked by id
x_campaign - id
x_transactions - cid - refers to the x_campaign id
x_airplay - cid - refers to x_campaign id
are the tables names and the rows names.
Cheers
I should also let you know,
I should also let you know, that once the tables are joined. I need to be able to group things like how many times was that campaign played.
what is the total spent of the campaign.
This is what I got but I get
This is what I got but I get this error
SELECT t1.cid as cid,t2.id as id,
t2.cname as cname,
t2.status as status,
t2.budget as budget,
t2.createstamp as createstamp,
FROM tbl_radio_ad_network_airplay as t1 INNER JOIN tbl_radio_ad_network_campaign as t2 ON t2.id = t1.cid
SQL query:
SELECT t2.cid AS cid, t1.id AS id, t1.cname AS cname, t1.status AS
STATUS , t1.budget AS budget, t1.createstamp AS createstamp,
FROM tbl_radio_ad_network_campaign AS t1
INNER JOIN tbl_radio_ad_network_airplay AS t2 ON t1.id = t2.cid
LIMIT 0 , 30
MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM tbl_radio_ad_network_campaign as t1 INNER JOIN tbl_radio_ad_network_airplay' at line 7
This is what I have the
This is what I have the first one works. the second code for some reason shows the result but some how gets the lno wrong and only shows row 1
SELECT
t1.id as aid,
t1.cname as cname,
t1.budget as budget,
t1.status as status,
t1.createstamp as createstamp,
t2.id as airid,
t2.cid as cid,
SUM(t2.lno) as lno,
FROM
tbl_radio_ad_network_campaign as t1
INNER JOIN tbl_radio_ad_network_airplay as t2 ON t1.id=t2.cid
GROUP BY t1.id
You will notice I have not changed any of the code just added a bit
SELECT
t1.id as aid,
t1.cname as cname,
t1.budget as budget,
t1.status as status,
t1.createstamp as createstamp,
t2.id as airid,
t2.cid as cid,
SUM(t2.lno) as lno,
t3.cid,
SUM(t3.amount) as amountspent
FROM
tbl_radio_ad_network_campaign as t1
INNER JOIN tbl_radio_ad_network_airplay as t2 ON t1.id=t2.cid
JOIN
tbl_radio_ad_network_transactions as t3
ON t2.cid=t3.cid
GROUP BY t1.id
What should show the lno as 77 returns a value of 154 and a amountspent of 201.86 when it should only be 100.93
I have no idea what is wrong...
I thought I would also give
I thought I would also give you some sample mysql data to help solve the issue I am having
Campaign Table
CREATE TABLE IF NOT EXISTS `tbl_radio_ad_network_campaign` (
`id` int(11) NOT NULL auto_increment,
`uid` int(11) NOT NULL,
`cname` varchar(100) NOT NULL,
`status` int(1) NOT NULL default '0',
`budget` varchar(50) NOT NULL,
`createstamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`rand` varchar(40) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `rand` (`rand`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `tbl_radio_ad_network_campaign`
--
INSERT INTO `tbl_radio_ad_network_campaign` (`id`, `uid`, `cname`, `status`, `budget`, `createstamp`, `rand`) VALUES
(1, 1, 'russell', 1, '29999', '2009-11-25 07:34:58', '4f1932519a941ba4f91bc146973912557fee619e'),
(2, 1, 'Peter Zapfella', 1, '10000', '2009-11-25 07:43:30', 'f79d1faf8e1c8bd5c1ceaaeeb3f32fd36873630b');
Airplay table
--
-- Table structure for table `tbl_radio_ad_network_airplay`
--
CREATE TABLE IF NOT EXISTS `tbl_radio_ad_network_airplay` (
`id` int(11) NOT NULL auto_increment,
`cid` int(11) NOT NULL,
`sid` int(11) NOT NULL,
`datestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`lno` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `tbl_radio_ad_network_airplay`
--
INSERT INTO `tbl_radio_ad_network_airplay` (`id`, `cid`, `sid`, `datestamp`, `lno`) VALUES
(1, 1, 1, '2009-11-25 20:47:07', '34'),
(2, 1, 2, '2009-11-25 21:02:28', '43'),
(3, 2, 0, '2009-11-25 21:49:45', '0');
transactions table
CREATE TABLE IF NOT EXISTS `tbl_radio_ad_network_transactions` (
`id` int(11) NOT NULL auto_increment,
`cid` int(11) NOT NULL,
`fid` int(11) NOT NULL,
`tid` int(11) NOT NULL,
`amount` varchar(100) NOT NULL,
`datestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `tbl_radio_ad_network_transactions`
--
INSERT INTO `tbl_radio_ad_network_transactions` (`id`, `cid`, `fid`, `tid`, `amount`, `datestamp`) VALUES
(1, 1, 0, 0, '0', '2009-11-25 21:51:14'),
(2, 3, 0, 0, '0', '2009-11-25 21:51:14'),
(3, 1, 0, 0, '100.93', '2009-11-25 23:06:51');
Hi Russell, Let's say you
Hi Russell,
Let's say you wanted to query the x_transactions table but to include in the results a campaign name. The query would look something like:
SELECT x_transactions.*,x_capaign.name FROM x_transactions,x_capaign WHERE x_transactions.cid = '$cid' AND x_transactions.cid,x_capaign.id;(don't forget to make sure that $cid is database safe with mysql_escape_string of course!)
Hope this helps!
Cheers,
David.