Reputation: 4171
I have three tables that have common affiliate_id
field:
impressions, clicks, commissions
I want to select all the data from these three tables where the affiliate_id = '$iAid' in one query, then handle the data in php.
How would I write a query like this?
Upvotes: 0
Views: 140
Reputation: 881
Here's PHP code assuming you're using MYSQL:
$qry = "SELECT im.*, cl.*, co.*
FROM `impressions` im, `clicks` cl, `comissions` co
WHERE im.`affiliate_id`=cl.`affiliate_id`
AND cl.`affiliate_id`=co.`affiliate_id`
AND im.`affiliate_id`='".$iAid."'";
Upvotes: 1
Reputation: 7011
How about a join:
select * from impressions, clicks, commissions
where impressions.affiliate_id = clicks.affiliate_id
and clicks.affiliate_id = commissions.affiliate_id
and impressions.affiliate_id = '$iAid'
Upvotes: 0
Reputation: 35181
select * from
impression a
join clicks b on (a.affiliate_id = b.affiliate_id)
join commissions c on (a.affiliate_id = c.affiliate_id)
where a.affiliate_id = ?
Upvotes: 0
Reputation: 1003
Do a left join in mysql query:
Select * from Impressions imp
Left join clicks c on c.affiliate_id = imp.affiliate_id
Left join commissions com on com.affiliate_id = imp_affiliate_id
Where ***
limit ***
ect....
Upvotes: 0
Reputation: 541
SELECT * FROM impressions AS i JOIN clicks AS c ON i.affiliate_id = c.affiliate_id JOIN commissions AS m ON i.affiliate_id = m.affiliate_id WHERE i.affiliate_id = '$iAid'
should do the trick
Upvotes: 0
Reputation: 82953
SELECT <YOUR COLUMN LIST>
FROM impressions a, clicks b, commissions c
WHERE a.affiliate_id = '$iAID'
AND a.affiliate_id = b.affiliate_id
AND b.affiliate_id = c.affiliate_id
Upvotes: 0