Help in JOIN

[vibhaJ]

This is my two tables:

CREATE TABLE `subject` (
  `id` int(11) NOT NULL auto_increment,
  `subjectName` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `subject`
--

INSERT INTO `subject` (`id`, `subjectName`) VALUES
(1, 'Maths'),
(2, 'Science'),
(3, 'English'),
(4, 'Chemistry');

CREATE TABLE `user_subject` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `user_subject`
--

INSERT INTO `user_subject` (`id`, `name`, `subject`) VALUES
(1, 'John', '2,3'),
(2, 'Riya', '1,3,4'),
(3, 'Mack', '1'),
(4, 'Nik', '1,2,3,4');

I want output like:

Name | Subjects
-------------------------------------
John | Science,English
Riya | Maths,English,Chemistry
Mack | Maths
Nick | Maths,Science,English,Chemistry

Please help me to create join for this.
Thanks.

[tyson.crouch]

G'day vibhadevit,

First off, there is a flaw within your database design.

CREATE TABLE `subject` (
  `subjectID` int(11) NOT NULL auto_increment,
  `subjectName` varchar(255) NOT NULL,
  PRIMARY KEY  (`subjectID`)
) ENGINE=InnoDB;

CREATE TABLE `student` (
  `studentID` int(11) UNSIGNED NOT NULL auto_increment,
  `studentName` VARCHAR(255) NOT NULL,
  PRIMARY KEY  (`studentID`)
)ENGINE=INNODB;

CREATE TABLE `student_subject` (
  `studentID` int(11) NOT NULL,
  `subjectID` int(11) NOT NULL,
FOREIGN KEY (studentID) REFERENCES student(studentID)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (subjectID) REFERENCES subject(subjectID)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB;

from here you may run you select queries

[tesuji]

I completely agree with tyson.crouch. To guarantee referential integrity, all foreign keys must be correctly defined. Unfortunately, there is a further very serious flaw in below table user_subject:

CREATE TABLE `user_subject` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `user_subject`
--

INSERT INTO `user_subject` (`id`, `name`, `subject`) VALUES
(1, 'John', [B]'2,3'[/B]),
(2, 'Riya', [B]'1,3,4'[/B]),
(3, 'Mack', [B]'1'[/B]),
(4, 'Nik', [B]'1,2,3,4'[/B]);

The many-to-many relationship between subject and student is constructed by the bold-faced list of keys. This is sort of horizontal repeating group, what means that this table does not fulfill first normal form. But most important: such coded relationship does not allow to create any useful query, for example a query for getting a list of students and their assigned subjects is hardly to write down. How should this query be designed, only consider that the numbers of assined subjects are varying from one to four?

Therefore this table must be redesigned. Hint: Because of many-to-many relationship we actually have these three tables:

1. subject

2. student

3. student_subject

where the latter one defines the many-to-many relationship. This table has at least two columns: studentid and subjectid which make the primary key of this table and they are also foreign keys concurrently.

I will give some further hints later.

-- tesu

[tesuji]

Ah sorry tyson.crouch!

I ought to have read your posting really more precisely. You have already given a complete correction of vibhadevit's table design. I was to much focused on his unfavorably designed table `user_subject`.

-- tesu

[vibhaJ]

Thank u guys for response and help.
But i am working on existing project where there are thousand of such entries.
It is difficult to normalize this table at this point.

So i just want to make search query based on subject all rest coding is already there.

Name | Subjects
-------------------------------------
John | Science,English
Riya | Maths,English,Chemistry
Mack | Maths
Nick | Maths,Science,English,Chemistry

I know it is not good practice, but i am also bounded with time.
Any help will be appreciated.

[tesuji]

I am afraid this can't be done in a plain select statement. You need some procedural code for parsing the string which contain the subject numbers and translating these numbers into subject names. A userdefined function can easily solve this problem, in principle:

create function parseandreplace (in vName varchar(50)) returns varchar(255) 
begin

... some declarations and initialisations here ...

-- get subject string
select subject into vSubject from user_subject where name = vName;

-- put a ',' on right end of subject string
set vSubject = INSERTSTR(length(vSubject)+1, vSubject, ','); 
 
-- for each subject number in vSubject do:
WHILE i < length(vSubject) LOOP

  -- position of next comma
  set comma = locate (vSubject, ',', i);

  -- get next subject number
  set vID = cast(substr(vSubject, i, comma-i) as integer);  

  -- get name of subject  
  select subjectName into vsubjectName from subject where id = vID;

  -- concat names
  set vsubjectNames = vsubjectNames || ', ' || vsubjectName;

  -- scip to next subject number
  set i = comma+1; 
END LOOP;

-- return concat names
RETURN vsubjectNames;
END;

-- usage
select name, parseandreplace(name) from  user_subject;

Well, above code should work in principle. Similar things could also be done in php. As for a UDF effort for programming and testing should not exceed one hour.

-- tesu

[tesuji]

Hi

I have just finished above outlined code also your both tables installed. It works, result is:

/*
select user_subject.Name as "Name", ParseAndReplace(user_subject.Name) from user_subject;

Name    ParseAndReplace(user_subject.Name)
------------------------------------------
John    Science, English
Riya    Maths, English, Chemistry
Mack    Maths
Nik     Maths, Science, English, Chemistry
*/

I did it on a Sybase database, I am just sitting in front of it.

-- tesu

[vibhaJ]

Thank u so much tesuji.
Actually i have never used function, procedure, view kind of stuff in mysql, But love to learn.
I have posted your code in sql window of phpmyadmin but it is throwing error.
Can u elaborate more on functions.

[tesuji]

I am sorry to have kept you waiting. (I am a bit under stress)

The older code I posted should only show how to solve the problem in principle. Therfore it was rather incomplete.

Here is the complete code of function ParseAndReplace. Consider that all local variables must be declared first. Also this code is for Sybase and MS SQL server, you need to check these functions: length, INSERTSTR, substr, locate whether they exist - and important too - if they exist, carefully check the parameter lists for mysql has its very own order of parameters in standard functions. cast(..) function is standard sql, which is supported by mysql.

I plan to program a generic ParseAndReplace function for such thing is often required.

create function ParseAndReplace (in vName varchar(50)) returns varchar(255) 
begin
declare i integer;
declare vSubject varchar(255);
declare vsubjectName varchar(255);
declare vsubjectNameConcat varchar(255);
declare vSubjectLength integer;
declare comma integer;
declare vID integer;
set i = 0;
select subject into vSubject from user_subject where name = vName;
set vSubjectLength = length(vSubject)+1;
set vSubject = INSERTSTR(vSubjectLength, vSubject, ',');  
set vsubjectNameConcat = '';
while i < vSubjectLength loop
  set comma = locate (vSubject, ',', i);
  set vID = cast(substr(vSubject, i, comma-i) as integer);  
  select subjectName into vsubjectName from subject where id = vID;
  if ( vsubjectNameConcat = '' ) then set vsubjectNameConcat = vsubjectName; 
   else set vsubjectNameConcat = vsubjectNameConcat || ', ' || vsubjectName;
  end if;
  set i = comma+1; 
end loop;
return vsubjectNameConcat;
end;

I hope this little function will help you.

-- tesu