CODEWITHSUNDEEP
mysqlselectjoincount
Colin R. Turner
Colin R. Turner

Reputation: 1415

Get data from one table and count matching records from another

I'm not sure if this is possible. I have one table members and a second table transactions.

I need to get the name of the member from the members table, but also count the number of transactions that member has made from another table. Is this even possible in a JOIN statement, or do I need to write two statements?

SELECT
    m.first_name,
    m.last_name,
    COUNT(t.giver_id),
    COUNT(t.getter_id)
FROM
    members AS m
JOIN
    transactions AS t
ON
    m.id = t.giver_id
WHERE
    m.id = $i

I should add that it's possible a member has not made any transactions and would therefore not appear in the transactions table.

When I run this code, it returns all NULL columns. When I add the EXPLAIN statement, MySql says "Impossible WHERE noticed after reading const table..."

Is this possible? If so, then what am I doing wrong? Thanks in advance.

EDIT: Sample data structure and expected output:

members
id  |  first_name  |  last_name
_______________________________
1   |     Bill     |    Smith
2   |     Joe      |    Jones

transactions table
id  |  giver_id  |  getter_id  |  status
________________________________________
1   |      1     |      2      |  complete
2   |      1     |      2      |  complete

So running my query should return:

1   |   Bill   |   Smith   |   2   |   0
2   |   Joe    |   Jones   |   0   |   2

Upvotes: 0

Views: 50

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Simple LEFT JOIN should suffice:

SELECT
  m.first_name,
  m.last_name,
  SUM(CASE WHEN m.id = t.giver_id THEN 1 END) AS giver_count,
  SUM(CASE WHEN m.id = t.getter_id THEN 1 END) AS getter_count
FROM members AS m
LEFT JOIN transactions AS t ON m.id = t.giver_id OR m.id = t.getter_id
GROUP BY m.first_name, m.last_name

Do not forget adding GROUP BY when using aggregate functions. Just because MySQL allows the query to go through without it, it doesn't mean it is advised. MySQL will pick up random row values for unaggregated columns which can be problematic. Avoid this anti-pattern.

Upvotes: 1

Related Questions