joining multiple columns in one table to a single column in another table

[dishadcruze]

Hi,

 

I have two tables users and teams

 

users

 

teams 

 

And i want to join them and display result like this

 

But not getting how to add same field twice

 

 

 

 

 

[Jacques1]

Before you do anything, you need to learn the basics of relational databases, especially normalization.

 

SQL tables aren't Excel spreadsheets where you store data however you feel like. There are specific rules which are very important for protecting the integrity of your data and avoiding anomalies.

 

Your “teams” table doesn't really contain teams. It's a strange mixture of team-related data and member assignments. It's not even clear what a team in your model really is. Appearently you assume that a manager leads exactly one team, and your teams only exist implicitly as manager-to-member assignments, but this is going to be a huge problem when the manager changes. Your comma-separated lists are also an Excel-ism which doesn't make sense in SQL.

 

This is how a sane layout would look like:

 

users

• user_id
• ...

teams

• team_id
• leader; foreign key pointing to users (user_id)
• ...

team_members

• team_id; foreign key pointing to teams (team_id)
• user_id; foreign key pointing to users (user_id)

Edited May 22, 2017 by Psycho

[dishadcruze]

@Jacques1:

 

Sir, i have stored my data with foreign key and primary key constraints in mysql . But i just shared the schematic which i have designed in excel.

 

Instead of using one more table called team_members, i thought of using a table teams and users. In teams table my muid coulmn will be the manager , and under that what all the users stored are the team members.

 

Can i do that way? or u suggest me to have 3 tables?

[Jacques1]

Like I said, your current layout is wrong. You'll see that yourself when you start adding columns for team-specific data. For example, let's say each team has a contact e-mail address. You cannot store that in your table, because you would have to repeat the same team address for each member entry, creating redundant data, possible conflicts and update anomalies.

 

You must have three tables.

 

But i just shared the schematic which i have designed in excel.

 

I'm not talking about your graphics. I'm saying that your way of thinking comes from the world of spreadsheets and doesn't make sense for relational databases.

[taquitosensei]

it doesn't make sense but it can be done. Think this should work. Didn't have a way to test it. 

select 
concat_ws(' ',manager.firstname,manager.lastname) as Manager,
group_concat(concat_ws(' ',players.firstname,players.lastname)) as team 
FROM 
teams 
JOIN users manager on teams.manager=manager.id 
JOIN users players on teams.team=players.id 
group by teams.manager