CODEWITHSUNDEEP
mysql
nick cruse
nick cruse

Reputation: 141

Select and count from two tables

How can select and count from two tables using mysqli query loop.

Here is the table structure

table1 = categories 
id | catname
-------------
1  | cat1
2  | cat2
3  | cat3

so on.

table2 =  articles
id | article | catid
---------------------
1  | art1    | 2
2  | art2    | 2
3  | art3    | 1
4  | art4    | 3

I need this to be displayed like

cat 1 - 1 articles
cat 2 - 2 articles
cat 3 - 1 articles

Can anyone point me out how to do this using mysqli query?

Upvotes: 2

Views: 194

Answers (3)

Kumar Saurabh Sinha
Kumar Saurabh Sinha

Reputation: 870

try this:

SELECT CONCAT(C.catname, ' - ', A.articles, ' Articles')
FROM   categories C
       INNER JOIN articles A
         ON C.id = A.catid
GROUP  BY A.catid
ORDER  BY C.catname

Upvotes: 0

Sergio
Sergio

Reputation: 6948

Try this

SELECT 
    c.catname,
    COUNT(*)
FROM categories c
INNER JOIN articles a
    ON c.id = a.catid
GROUP BY 
    c.catname

Upvotes: 1

Taryn
Taryn

Reputation: 247870

If you want this in a single column then you can use the following:

select 
  concat(c.catname, ' - ', a.Total, ' articles') list
from categories c
inner join
(
  select count(*) Total,
    catid
  from articles
  group by catid
) a
  on c.id = a.catid

See SQL Fiddle with Demo

Or you can do this without the subquery:

select 
  concat(c.catname, ' - ', count(*), ' articles') list
from categories c
inner join articles a
  on c.id = a.catid
group by c.catname;

See SQL Fiddle with Demo. The result is:

|              LIST |
---------------------
| cat1 - 1 articles |
| cat2 - 2 articles |
| cat3 - 1 articles |

Upvotes: 4

Related Questions