I have a database with two tables groups and members. The columns from these tables are below.
Groups table: group_id,group_name
Members table: member_id,group_id,status
I have an app that needs to display a list of groups along with the groups members. What is the most efficient way to say SELECT * from groups then for each group/row returned do another select on the members table to query the members (for example SELECT * FROM members WHERE group_id=X).
select
g.group_id,
g.group_name,
m.member_id,
m.status
from groups g
inner join members m on g.group_id = m.group_id
Assuming that both members and groups have a column named group_id, you can use join to fetch the results you want:
select m.*
from members as m
inner join groups as g on m.group_id = g.group_id
where group_id = X
SELECT *
FROM groups g
LEFT JOIN members m
ON g.group_id = m.group_id
This would return all groups and their members.
You can introduce variables, so for example
SET @groups = (SELECT TOP 1 group_id FROM groups)
and add it to your query.
SELECT *
FROM groups g
LEFT JOIN members m
ON g.group_id = m.group_id
WHERE g.group_id = @groups
top? Isn't that for SQL Server? I think you mean select group_id from groups limit 1)
JOIN? If not, good place to start.