CODEWITHSUNDEEP
phpmysqlsql
Alex
Alex

Reputation: 9265

subqueries: select from one table based on matching user ids with team ids and output 2 dimensional array

I want to select from login_users to get the following fields: name, username, and user_id.

I have a separate table teams with the team_id, as well as the associated user_id, so for example I might have bob with team1, jack with team2, jim with team1, and paul with team2 (all separate rows, not bob and paul -> team2). If I provide a function with the team_ id I want to be able to find all the associated user_ids, and then match that data with login_users to output a two dimensional array that has name, username, and user_id of all the team members.

SELECT username, user_id, name FROM login_users WHERE EXISTS ( SELECT user_id FROM teams WHERE team_id= $team_id );

I know it has something to do with sub queries I just have never done them before, and I feel that I am on the wrong track. Furthermore, how would I then go about producing a two dimensional array?

Upvotes: 1

Views: 200

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269723

If you want to do this with an "exists" subquery, then it needs to be a correlated subquery:

SELECT username, user_id, name
FROM login_users
WHERE EXISTS (SELECT user_id
              FROM teams
              WHERE team_id = $team_id and
                    teams.user_id = login_users.team_id
             );

You are missing the condition on the user_id. That version of the query is saying "get me all rows from login_users when team_id exists in teams".

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Why only subquery ?? why not JOIN

SELECT u.username, u.user_id, u.`name` FROM login_users u
JOIN teams t ON(u.user_id = t.user_id)
WHERE team_id= $team_id

Upvotes: 2

Related Questions