Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL joins and COUNT(*) from another table

I have two tables: groups and group_members.

The groups table contains all the information for each group, such as its ID, title, description, etc.

In the group_members table, it lists all the members who are apart of each group like this:

group_id | user_id 1 | 100 2 | 23 2 | 100 9 | 601 

Basically, I want to list THREE groups on a page, and I only want to list groups which have MORE than four members. Inside the <?php while ?> loop, I then want to four members who are apart of that group. I'm having no trouble listing the groups, and listing the members in another internal loop, I just cannot refine the groups so that ONLY those with more than 4 members show.

Does anybody know how to do this? I'm sure it's with MySQL joins.

like image 401
hohner Avatar asked Jan 31 '11 02:01

hohner


People also ask

How do I join two tables and counts in SQL?

To achieve this for multiple tables, use the UNION ALL. select sum(variableName. aliasName) from ( select count(*) as yourAliasName from yourTableName1 UNION ALL select count(*) as yourAliasName from yourTableName2 ) yourVariableName; Let us implement the above syntax.

How fetch data from two tables in join MySQL?

Ans: Joining two tables in SQL can be done in four major ways: Inner Join (returns rows with matching columns), Left Join (ALL records in the left table and matching records in the right table), Right Join (ALL records in the right table and matching records in the left table), and Union (removes duplicates).

What are SQL joins how do you use them to fetch data from multiple tables?

SQL JOIN. A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

Which join type will show a record from both tables if both tables have a matching record?

Show rows where a common value exists in both of the joined tables. If you want to show only those rows that have matching values in the joined field, you use an inner join.


1 Answers

MySQL use HAVING statement for this tasks.

Your query would look like this:

SELECT g.group_id, COUNT(m.member_id) AS members FROM groups AS g LEFT JOIN group_members AS m USING(group_id) GROUP BY g.group_id HAVING members > 4 

example when references have different names

SELECT g.id, COUNT(m.member_id) AS members FROM groups AS g LEFT JOIN group_members AS m ON g.id = m.group_id GROUP BY g.id HAVING members > 4 

Also, make sure that you set indexes inside your database schema for keys you are using in JOINS as it can affect your site performance.

like image 145
Nazariy Avatar answered Sep 25 '22 15:09

Nazariy