CODEWITHSUNDEEP
mysqljoindistinct
Rooster
Rooster

Reputation: 10077

Count number of distinct particuliar right columns in a left join

Given the following 2 tables:

<div style="float:left;width:48%;">
<label>Table1</label>
<table>
  <thead style="background:green;">
    <tr>
      <th>ID</th>
      <th>Name</th>
    </tr>
  </thead>
  <tbody style="background:yellow;">
    <tr>
      <td>1</td>
      <td>Apple</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Orange</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Banana</td>
    </tr>
  </tbody>
</table>
</div>
<div style="float:left;width:48%;">
  <label>Table2</label>
<table>
  <thead style="background:green;">
    <tr>
      <th>t1ID</th>
      <th>Color</th>
    </tr>
  </thead>
  <tbody style="background:yellow;">
    <tr>
      <td>1</td>
      <td>Red</td>
    </tr>
    <tr>
      <td>1</td>
      <td>Green</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Yellow</td>
    </tr>
    <tr>
      <td>1</td>
      <td>Red</td>
    </tr>
  </tbody>
</table>
</div>

I'm trying to create a join that also contains a count of distinct matches.

So I'd be looking for a result set like this:

<label>Results</label>
<table>
  <thead style="background:green;">
    <tr>
      <th>ID</th>
      <th>Name</th>
      <th>ColorCount</th>
    </tr>
  </thead>
  <tbody style="background:yellow;">
    <tr>
      <td>1</td>
      <td>Apple</td>
      <td>2</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Orange</td>
      <td>0</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Banana</td>
      <td>1</td>
    </tr>
  </tbody>
</table>

My basic query is:

SELECT Table1.ID, Table1.Name, COUNT(DISTINCT Table2.Color) AS ColorCount FROM
Table1 LEFT JOIN Table2 ON Table1.ID=Table2.t1ID

However this only return a row of data like:

1    Apple    3

The above is counting all the distinct colors, rather than the 2 red(1) and 1 green(1) (1+ 1) = 2;

It appears the distinct is forcing the query to distinct all the values from Table2 rather than those that match the conditions of the join. I'm banging my head here. Thinking about my end goal, I can definitely achieve this using sub queries, but If I don't need to use them in tandem with a join, that'd be great.

Cheers

Upvotes: 0

Views: 43

Answers (1)

radar
radar

Reputation: 13425

You can use GROUP BY and LEFT JOIN

SELECT ID , T1.Name, COUNT(DISTINCT T2.Color) as ColorCount
FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.ID = T2.t1ID
GROUP BY ID,T1.NAME

Upvotes: 1

Related Questions