CODEWITHSUNDEEP
mysql
tmartin314
tmartin314

Reputation: 4171

Three table query MySQL

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

Answers (6)

jpwco
jpwco

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

Nate Reed
Nate Reed

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

tpdi
tpdi

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

Simon marc
Simon marc

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

Ogapo
Ogapo

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

Chandu
Chandu

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

Related Questions