CODEWITHSUNDEEP
phpmysqljoin
Brandon Tyler Jones
Brandon Tyler Jones

Reputation: 49

Inner Joining with two tables

I need a little help setting up my query. I'm simply trying to access the amount of people who are in the same 'clan' by joining these two tables together, clan, users. Each users has a column 'clan' which is the same as the table clan's column 'roomOwner' and then I'm trying to get the table clan's information along with the amount of members so it would be like: room, roomOwner, members So basically all I have is this: 

SELECT c.*, count(u.clan) AS members FROM clans c inner join users u WHERE c.roomOwner = u.clan ORDER BY members;

It only shows one clan though. Any help please?

Upvotes: 0

Views: 130

Answers (3)

Joe Brown
Joe Brown

Reputation: 637

Perhaps this will help: select c.*, count(links.id) as members from clans c left join users u on c.roomOwner = u,clan group by u.clan order by members

Upvotes: 0

John Woo
John Woo

Reputation: 263693

Your query has no GROUP BY clause. and I think it's only returning single record right? LEFT JOIN is needed here since there are possibilities that a clan has no member.

SELECT  b.roomOwner, COUNT(a.clan) memberCount
FROM    clan b
        LEFT JOIN users a
            ON a.clan = b.roomOwner
GROUP BY b.roomOwner
ORDER BY memberCount

Upvotes: 1

Elena Sharovar
Elena Sharovar

Reputation: 356

You forgot GROUP BY. Do you have some "id" column in "clans" table? Group by that "id"

SELECT c.*, count(u.clan) AS members 
FROM clans c 
inner join users u ON c.roomOwner = u.clan
GROUP BY clans.id

And you need LEFT JOIN there instead of INNER JOIN if you want to see info about all clans, even having 0 users.

Upvotes: 0

Related Questions