MySQL joins count matched record from another table

Question

What I have is 2 tables, the first table I want it to display all results, no "where" or anything to limit it.

The second table I want to match an id to the first table, it can have multiple rows referencing it so I want to count the number.

So lets say the first table is like this:

ID - name
1  - one
2  - two
3  - three
4  - four

And the second table is like this

ID - REF
1  - 1
2  - 1
3  - 2
4  - 2
5  - 3
6  - 3
7  - 4
8  - 4

I want to combine them like so:

ID - name - count
1  - one  - 2
2  - two  - 2
3  - three- 2
4  - four - 2

I have tried using subqueries, left joins, right joins, inner joins, sub query joins, grouping and 9 times out of ten I get 20 results of the first ID out of 1300 results I should get. The rest I only get an incorrect count and no name.

I feel this is MySQL 101 but after attempting multiple variations and coming up with nothing I feel there must be something I am missing.

I would be happy to be directed to a question that is in the exact same situation (2 hours of looking and nothing that works exactly like this) Or a simple query to point out the logic of this method, Thanks in an advance to anyone that answers, you will have made my day.

If any additional information is needed let me know, I have left out the query deliberately because I have adapted it so many times that it will not have much relevance (I would have to list every query I tried and that would be far to much scrolling)

Ok I have tested the first and answer and it seemed to work in this context so I will expand my answer, the question is "answered" so this is just an expansion if there are no replies I will close this with the answer as follows:

SELECT t.id, t.name, count(*) AS suppliers 
FROM #__tiresku AS t 
LEFT JOIN #__mrsp AS m ON t.name = m.tiresku_id 
GROUP BY t.id, t.name

The expansions is an inner join, I have another table that is more of a list, it has an id and a name and that's it, I reference that table with an id to get the "name" instead.

This might have a better option then joins (like foreign keys or something).

I had this added to the select b.name AS brand_name

And a join INNER JOIN #__brands AS b ON t.brand = b.id

Worked with a sub query rather then join

Gordon Linoff · Accepted Answer · 2013-07-18 17:34:04Z

6

This is a basic join with aggregation:

select t1.id, t1.name, count(*) as `count`
from table1 t1 join
     table2 t2
     on t1.id = t2.ref
group by t1.id, t1.name;

answered Jul 18, 2013 at 17:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jordan Ramstad Over a year ago

I will test it out and let you know how it goes. If it does not work I will update the question to show why this example did not work.

Jordan Ramstad Over a year ago

Seemed to work, though I will expand my question as I have found the root of the problem.

mckenzm · Accepted Answer · 2014-01-15 06:55:48Z

0

As asked, the example does not include records in the first table that are not in the second table, but this may be possible and is implied.

I am inclined to create a nested table of the counts in the second table without regard to "exists in the first table" either, unless the counts are huge and then the probe becomes cheaper.

I would do the count of the values in the second table first as the first table is a defacto decode of a description.

select ID, name, coalesce('count',0)

from (select ref, count(*) as 'count'
      from table2
      group by ref) as T2

right join table1

on ref = ID;

answered Jan 15, 2014 at 6:55

mckenzm

1,8682 gold badges14 silver badges21 bronze badges

Collectives™ on Stack Overflow

MySQL joins count matched record from another table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related