Jump to content

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


Recommended Posts

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 by Psycho
Link to post
Share on other sites

@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?

Link to post
Share on other sites

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.

Link to post
Share on other sites
  • 2 weeks later...

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
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.