I have a MySQL database that looks like this:
( id , name )
( id , name )
( id , group_id , user_id )
select * from 'group_users' where 'user_id' = 47;
( 1 , 3 , 47 ),
( 2 , 4 , 47 ),
In general, you want to reduce the total number of queries to the database, by making each query do more. There are many reasons why this is a good thing, but the main one is that relational database management systems are specifically designed to be able to join tables quickly and are better at it than the equivalent code in some other language. Another reason is that it's usually more expensive to open many little queries than it is to run one large query that has everything you'll end up needing.
You want to take advantage of your RDBMS's strengths, so you should try to push data access into it in a few big queries rather than lots of little queries.
Now, that's just a general rule of thumb. There are cases when it's better to do some things outside of the database. It's important that you determine which is the right case for your situation by looking into bottlenecks if and only if they occur. Don't spend time worrying about performance until you find a performance problem.
But, in general, it's better to handle joins, lookups and all other query-related tasks in the database itself than it is to try to handle it in a general-purpose language.
That said, the kind of join you want is an inner join. You'd structure your join query like this:
SELECT groups.name, group_users.user_id FROM group_users INNER JOIN groups ON group_users.group_id = groups.group_id WHERE groups.user_id = 47;