CODEWITHSUNDEEP
mysqljoininner-join
Awais Qarni
Awais Qarni

Reputation: 18006

Join on same column name

Hello there I want to get data from two tables that share same column name. My table structure are

Table patients

 ---------------------------------------
|  id  |  affiliate_id  |  somecolumn  |
 ---------------------------------------
|  1   |    8           |  abc         |          
 ---------------------------------------
|  2   |    8           |  abc         |          
 ---------------------------------------
|  3   |    9           |  abc         |          
 ---------------------------------------

Table Leads

 ---------------------------------------
|  id  |  affiliate_id  |  someothern  |
 ---------------------------------------
|  1   |    8           |  xyz         |          
 ---------------------------------------
|  2   |    8           |  xyz         |          
 ---------------------------------------
|  3   |    3           |  xyz         |          
 ---------------------------------------

Now my requirement was to get COUNT(ID) from both tables in a single query. I want result like 

 ----------------------------------------------------
|  affiliate_id  |  total_patients  |  total_leads  |
 ----------------------------------------------------
|       8        |        2         |       2       |          
 ----------------------------------------------------
|       9        |        1         |       0       |          
 ----------------------------------------------------
|       3        |        0         |       1       |          
 ----------------------------------------------------

I wrote following query

SELECT `p`.`affiliate_id`, COUNT(p.id) AS `total_patients`, 
   COUNT(cpl.id) AS `total_leads` 
FROM `patients` AS `p` 
   INNER JOIN `leads` AS `cpl` ON p.affiliate_id =cpl.affiliate_id  
GROUP BY `p`.`affiliate_id`

But I am not getting result . This query results giving only one affiliate with same number of total_patients and total_leads

Upvotes: 2

Views: 166

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146449

Two ways:

Select l.affiliate_id ,
      count(distinct p.id) patientCount,
      count(distinct l.id) LeadCOunt
From patients p Join leads l
    On l.affiliate_id = p.Affiliate_id
Group By l.affiliate_id

or, (assuming affiliates are in their own table somewhere) 

Select Affiliate_id,
    (Select Count(*) From Patients
     Where Affiliate_id = a.Affiliate_id) patientCount,
    (Select Count(*) From Leads
     Where Affiliate_id = a.Affiliate_id) LeadCount
From affiliates a

Upvotes: 0

Taryn
Taryn

Reputation: 247670

The problem is that you need to get a list of the distinct affiliate_id first and then join to your other tables to get the result:

select a.affiliate_id,
  count(distinct p.id) total_patients,
  count(distinct l.id) total_leads
from
(
  select affiliate_id
  from patients
  union 
  select affiliate_id
  from leads
) a
left join patients p
  on a.affiliate_id = p.affiliate_id
left join leads l
  on a.affiliate_id = l.affiliate_id
group by a.affiliate_id;

See SQL Fiddle with Demo

Upvotes: 3

Related Questions