CODEWITHSUNDEEP
phpmysqlpdo
DauntlessRob
DauntlessRob

Reputation: 795

How do I use one row to pull multiple other rows from the same mySQL table

So, lets say i have a user table. Each user has the ability to be in a team with upto 3 other users. So for now i have a column for each spot in the team(4 columns total, so your own id fills in a spot so you know where you fit in the team). And i put the ids to the other members of the team in each of the other columns. In the end, everyone on one team would have the same values in those 4 columns.

How would i query sql to look at those ids and pull the info for all the other users on there team (so by looking at one user, i can pull all 4 team members rows)? Is this the most efficient way of storing that data?

Upvotes: 0

Views: 143

Answers (2)

peterm
peterm

Reputation: 92785

Normalize your data from the beginning. It will pay off big time in a long run. This way you'll be able to normally maintain and query your data.

A proposed schema in a simplified form may look like this

CREATE TABLE users
(
  `user_id` int not null auto_increment primary key, 
  `user_name` varchar(5)
);  
CREATE TABLE teams
(
  `team_id` int not null auto_increment primary key, 
  `team_name` varchar(5)
);
CREATE TABLE team_users
(
  `team_id` int, 
  `user_id` int,
  primary key (team_id, user_id),
  foreign key (team_id) references teams (team_id),
  foreign key (user_id) references users (user_id)
);

If you need to pull all members for a team with a name 'team2'

SELECT t.team_id, t.team_name, u.user_id, u.user_name
  FROM team_users tu JOIN teams t
    ON tu.team_id = t.team_id JOIN users u
    ON tu.user_id = u.user_id
 WHERE t.team_name = 'team2'

If you need to get all members of a team where user with user_id = 2 is a member

SELECT t.team_id, t.team_name, u.user_id, u.user_name
  FROM team_users tu JOIN team_users tu2
    ON tu.team_id = tu2.team_id JOIN teams t
    ON tu.team_id = t.team_id JOIN users u
    ON tu.user_id = u.user_id 
 WHERE tu2.user_id = 2

Sample output:

| TEAM_ID | TEAM_NAME | USER_ID | USER_NAME |
|---------|-----------|---------|-----------|
|       2 |     team2 |       2 |     user2 |
|       2 |     team2 |       4 |     user4 |
|       2 |     team2 |       5 |     user5 |

Here is SQLFiddle demo

Upvotes: 1

opatut
opatut

Reputation: 6864

I think first of all you should not care about this being the most efficient way of storing such data, but the most logical. MySQL is generally pretty good at being a relational database, so the following should perform extremely well:

Make two tables. One for users (with IDs), one for the teams.

In the team table, you put the 4 IDs of the users. You can put in a team ID and name or whatever if you like, but don't have to.

Then you find the team entry like this:

SELECT * FROM team WHERE u1 == ? OR u2 == ? OR u3 == ? or u4 == ?;

And then you query for the users separately.

To improve performance, you may then think about table joins, joining the user's data onto the team entry:

SELECT * from team 
    LEFT JOIN user user1 ON u1 == user1.id
    LEFT JOIN user user2 ON u2 == user2.id
    LEFT JOIN user user3 ON u3 == user3.id
    LEFT JOIN user user4 ON u4 == user4.id;

This will fetch you one row per team with all user details in it.

Even better: many to many

A many to many relationship has two tables (users and teams) and a relation table (team_users), that contains pairs of IDs and potential other values (e.g. position on the team).

Then you can map the user to his team, and get all the users (and additional values) from that, all using only the relation table. Using joins, you can again fetch your information along with the mapping, reducing the number of queries. MySQL is really good at this!

Upvotes: 0

Related Questions