CODEWITHSUNDEEP
phpmysqlsql
Smith
Smith

Reputation: 5961

selecting author in table1 and count published book in table2 mysql

I have two tables in a database that looks like this

members table1

id | memberid | name | password
-------------------------------

Journal table2

id | memberid | title | desc
-----------------------------

i want to select all members from table one, joining with the result journal_count of each member in table 2. Am trying to count the number of times each memberid appears in table2

Am using mysql and php, can someone help me with this query

thanks

Upvotes: 0

Views: 222

Answers (2)

Nicola Cossu
Nicola Cossu

Reputation: 56357

select m.memberid,m.name,count(j.memberid) as total
from members as m
left join journal as j
on m.memberid = j.memberid
group by m.memberid

P.S. If your table has a field named desc, beware that this is a reserved word and it would be better to rename it. Otherwise you'll always have to put it within backticks.

select m.memberid,m.name,count(j.memberid) as total
from members as m
left join journal as j
on m.memberid = j.memberid
where m.memberid = $memberid
group by m.memberid

Upvotes: 5

shankhan
shankhan

Reputation: 6571

select members.name, count(journal.memberid) as journal_count from members, journal where members.memberid = journal.memberid group by journal.memberid

Upvotes: 2

Related Questions