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.
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.
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).
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With