CODEWITHSUNDEEP
mysqlsql
André Ribeiro
André Ribeiro

Reputation: 59

How to do a query based on another query result

I would like know how to build a query based another query result.

for example:

I have this table named groups:

group_id | user_id | is_admin
  146    |    1    |    1
  146    |    3    |    1
  146    |    6    |    1
  146    |    7    |    1
  102    |    21   |    1
  105    |    174  |    1
  109    |    369  |    1

So, I execute this query to get all groups based on user_id

SELECT * FROM groups WHERE user_id = 6;

The result is?

group_id | user_id | is_admin
  146    |    6    |    1

But, I would like to get now, all groups based on group_id. So, I would like execute this query:

 SELECT * FROM groups where group_id = 146;

So, I have this query result:

group_id | user_id | is_admin
  146    |    1    |    1
  146    |    3    |    1
  146    |    6    |    1
  146    |    7    |    1

In resume, I would like find which group or user belongs and select all rows that contain their group_id

1 - SELECT * FROM groups WHERE user_id = 6;
2 - SELECT * FROM groups WHERE groups in (146,102, ...);

NOTE: The user_id can belong to more than one group.

Thank you.

Upvotes: 1

Views: 527

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Use = and a subquery if you only expect one group per user (as in your sample data):

select g.*
from groups g
where g.group_id = (select g2.group_id from groups g2 where g2.user_id = 6);

Use in if a user could be in multiple groups:

select g.*
from groups g
where g.group_id in (select g2.group_id from groups g2 where g2.user_id = 6);

Upvotes: 2

Related Questions