Join table

Dev Shed Forums Sponsor:

July 21st, 2012, 04:21 AM

BismritI

Registered User

Join Date: Jul 2012

Posts: 2

Time spent in forums: 31 m 53 sec
Reputation Power: 0

Join table

I have two table as per given below

Code:

 
+------+------------------+-----------+----------+------------+
| ssId | ssRegistrationID | ssSubject | ssScheme | ssSchemeId |
+------+------------------+-----------+----------+------------+
|    1 |           100000 |         2 |      256 | 1,2        |
|    2 |           100001 |         2 |      256 | 1,4,3      |
|    3 |           100002 |         2 |      256 | 2,4,3      |
|    4 |           100003 |         3 |      128 | 3          |
|    5 |           100004 |         2 |      256 | 1,3,4      |
+------+------------------+-----------+----------+------------+

Code:

 
mysql> select scId, scName from _scheme limit 4;
+------+---------------------------------------------+
| scId | scName                                      |
+------+---------------------------------------------+
|    1 | Open Category (HSTFS)                       |
|    2 | Dalit (HSTFS)                               |
|    3 | Indigenous Nationalities (Janajati) (HSTFS) |
|    4 | Daughter of BPKIHS Faculty/Staff (HSTFS)    |
+------+---------------------------------------------+

and I want above table given below

Code:

|    1 |           100000 |         2 |      256 | Open Category (HSTFS),Dalit (HSTFS)

Please help me how to do this

July 21st, 2012, 04:34 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

Do you have control over the structure of the tables?

If so, normalise.

July 21st, 2012, 04:35 AM

BismritI

Registered User

Join Date: Jul 2012

Posts: 2

Time spent in forums: 31 m 53 sec
Reputation Power: 0

How to do that ? I am beginner to MySQL

Quote:

Originally Posted by cafelatte

Do you have control over the structure of the tables?

If so, normalise.

July 21st, 2012, 05:58 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 14 sec
Reputation Power: 4140

Quote:

Originally Posted by BismritI

How to do that ? I am beginner to MySQL

for background, search first normal form

what you need to do is remove the ssSchemeId column, because it violates first normal form

every column should be atomic, i.e. hold only one value

you need to create a relationship table to act as an intermediate table in between the ss table and the sc table

Code:

+------+------------+
| ssId | ssSchemeId |
+------+------------+
|    1 |    1       |
|    1 |    2       |
|    2 |    1       |
|    2 |    4       |
|    2 |    3       |
|    3 |    2       |
|    3 |    4       |
|    3 |    3       |
|    4 |    3       |
|    5 |    1       |
|    5 |    3       |
|    5 |    4       |
+------+------------+

so that where you had multiple values inside the single column, now there are multiple rows

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

Developer Shed Advertisers and Affiliates