Thread: Join table

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    9
    Rep 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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Do you have control over the structure of the tables?

    If so, normalise.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    9
    Rep Power
    0
    How to do that ? I am beginner to MySQL
    Originally Posted by cafelatte
    Do you have control over the structure of the tables?

    If so, normalise.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo