0

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column team.

   -----------------------------
   |ID|        Team      |Task |
   -----------------------------
   |1 |team1,team2,team5 |23   |
   |2 |team3,team2       |34   |
   |3 |team1,team5,team3 |45   |
   |4 |team2             |2    |
   |5 |team1,team3       |65   |
   |6 |team2,team5       |4    |
   |7 |team1             |34   |
   |8 |team5             |67   |
   ----------------------------- 

My question is that i like to check whether value in column team is subset of any of the value in same column team (It should not check with it own value in the column team), if that value comes out to be true then i would like to delete that row which has that value.It should run for all values of column team.

OUTPUT table will be like this

   ---------------------------
   |ID|    Team          |Task|
   ---------------------------
   |1 |team1,team2,team5 |23  |
   |2 |team3,team2       |34  |
   |3 |team1,team5,team3 |45  |
   ----------------------------

As you can see in team5 is subset of {team2,team5},{team1,team5,team3} and {team1,team2,team5}, Same with team1 ,{team2,team5} ,{team1,team3}, team2 they will also get removed because they are subset of some value in column team.

If there is any other approach to do this then do tell me. In initial Table i used group_concat function on it to get all values related to particular ID.

  • Is tem3,team2 supposed to be team3,team2 or is that intentional ? – RolandoMySQLDBA Nov 20 '14 at 19:44
  • @RolandoMySQLDBA : Sorry for Typo Error ,can you help me with this? – Kunal2229 Nov 20 '14 at 19:47
  • How many rows are in the Team table ? – RolandoMySQLDBA Nov 20 '14 at 19:58
  • @RolandoMySQLDBA : you can see that initial table contains only 8 row and output table contains 3 rows – Kunal2229 Nov 20 '14 at 20:01
  • @RolandoMySQLDBA : Please don't get confused with Table name Team and column name Team, just focus on column named Team – Kunal2229 Nov 20 '14 at 20:02
2

As DBNull pointed out, you are experiencing this challenge because the Team column is breaking First Normal Form. The values in that column should be atomic.

You have two options here. The first is to follow DBNull's recommendation of having the teams registered in their own Team table and having a separate TeamGroup table to store Team Groups. While you're at it you could throw in the third table to store the Tasks that the Team Groups are bound to (trusting they are bound to be changed from time to time and you'd like to know that history).

The second option is to write a script that does pretty much what you'd be doing in the first option except it reflects the change back to the table in the de-normalized format.

This isn't trivial. You'll need to loop over each row, split the column into the atomic team names and then query the table using a FIND_IN_SET() for each team name plus ensuring not to match the row your cursor is pointing to. Every time the table changes you'll need to re-run the query.

Here is some SQL that demonstrates normalizing the data. It has foreign keys for data consistency. The task IDs are not stored in the team_group table in this example instead a separate table exists which allows for multiple tasks.

CREATE TABLE `team` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL COMMENT 'Name of Team',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `team_group` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL DEFAULT '' COMMENT 'Optional Name of Team Group',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='a team group can be one or more teams';

CREATE TABLE `team_group_map` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `team_id` int(10) unsigned NOT NULL,
  `group_id` int(10) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_team_group` (`team_id`,`group_id`),
  KEY `group_id` (`group_id`),
  CONSTRAINT `team_group_map_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`) ON DELETE CASCADE,
  CONSTRAINT `team_group_map_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `team_group` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Binds teams to a team group';

CREATE TABLE `task` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `task_group_map` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `task_id` mediumint(8) unsigned NOT NULL,
  `group_id` int(10) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_group_task` (`group_id`,`task_id`),
  KEY `task_id` (`task_id`),
  CONSTRAINT `task_group_map_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `task` (`id`) ON DELETE CASCADE,
  CONSTRAINT `task_group_map_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `team_group` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Binds tasks to a team group';


-- Add a new team
INSERT INTO team (name) VALUES ('Team1');

-- Add a new group and add an existing team to it
INSERT INTO team_group (name) VALUES ('Euro east group');
-- Get ID for this group
SELECT id FROM team_group WHERE name = 'Euro east group';
-- Get team's ID
SELECT id FROM team WHERE name = 'Team1';
-- Add team to the group (team_id = 1, group_id = 1)
INSERT INTO team_group_map (team_id, group_id) VALUES (1, 1);

-- Add a task and set it for a group
INSERT INTO task (name) VALUES ('Win Finals');
-- Get ID for this task
SELECT id FROM task WHERE name = 'Win Finals';
-- Get team's ID
SELECT id FROM team WHERE name = 'Team1';
-- Add task to the group (task_id = 1, group_id = 1)
INSERT INTO task_group_map (task_id, group_id) VALUES (1, 1);

-- Select the teams and group IDs
SELECT team.name, group_id
FROM team_group_map
INNER JOIN team ON (team.id = team_id);

-- Select the teams and group names
SELECT team.name, group.name 
FROM team_group_map
INNER JOIN team ON (team.id = team_id)
INNER JOIN team_group AS `group` ON (group.id = group_id);

-- Select the groups, their task IDs and when the tasks were added to the group
SELECT group.name, task_id, map.updated_at
FROM task_group_map AS map
INNER JOIN team_group AS `group` ON (group.id = group_id);

-- Select the groups and their task Names + when tasks were added to group
SELECT group.name, task.name, map.updated_at
FROM task_group_map AS map
INNER JOIN task ON (task.id = task_id)
INNER JOIN team_group AS `group` ON (group.id = group_id);

-- Select the most recent task for a group
SELECT task.name, map.updated_at
FROM task_group_map AS map
INNER JOIN task ON (task.id = task_id)
WHERE group_id = 1
ORDER BY map.updated_at DESC
LIMIT 1;

0

This is not the answer you're hoping to hear but the comma-separated list in Team is not the way to go. Your table needs to look like the one below. It is going to be far easier to get the result you want starting from this table than from yours.

-----------------------------
|ID|        Team      |Task |
-----------------------------
|1 |team1 |23   |
|1 |team2 |23   |
|1 |team5 |23   |
|2 |tem3  |34   |
|2 |team2 |34   |
|3 |team1 |45   |
|3 |team5 |45   |
|3 |team3 |45   |
|4 |team2 |2    |
|5 |team1 |65   |
|5 |team3 |65   |
|6 |team2 |4    |
|6 |team5 |4    |
|7 |team1 |34   |
|8 |team5 |67   |
----------------------------- 

Even better, put the valid Team names into another table and put TeamID in your first table instead of Team. Then you can avoid missing the match on 'tem3'.

-----------------------------
|TeamID|        Team 
-----------------------------
|1 |team1 |
|2 |team2 |
|3 |team3 |
|4 |team4 |
|5 |team5 |
----------------------------- 
  • :i know that but according to algorithm ,i have to perform such type of step, i hope you will get something related to stored procedure of MYSQL because i searched a lot but can't get it from internet. – Kunal2229 Nov 20 '14 at 19:44
  • is there any type of matching occur for string in MySQL? – Kunal2229 Nov 20 '14 at 19:45
  • It's going to be a little complicated. Here's the logical steps I would take. Loop through each record in your table; Pull the value of ID and Team into variables; Split the Team var on the commas with functions like INSTR() and SUBSTRING(); Insert the sub-values into a temp table; Query the full table for records where ID does not match your current ID var but each of the sub-values in your temp table are present in Team; Add result to a list winners or losers; When done with loop report per your list. – DBNull Nov 20 '14 at 21:11
  • thanks for replying ,can you show me in practical way because i am new to Stored Procedure – Kunal2229 Nov 21 '14 at 6:16
  • there is nothing 'tem3' – Kunal2229 Nov 25 '14 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.