Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: Concatenate columns

    Hi,

    I have a table of courses like this:

    Courses
    --------
    - courseID (PK)
    - name
    - parentID
    - courseCodeID (FK)

    example:

    courseID--name------------------ parentID courseCodeID
    1---------Bachelor of Commerce
    2---------Marketing Management------1
    3---------Human Resource Mgmt------1
    4---------Bachelor of bla bla----------NULL

    What I need is put that in a drop down menu, hence I need to concatenate the courses name to the parent name like :
    - Bachelor of Commerce (Marketing Management)
    - Bachelor of Commerce (Human Resource Management)

    Any idea? Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT CONCAT(parent.name,' ('
                 , child.name,')') AS dropdown
      FROM Courses AS parent
    INNER
      JOIN Courses AS child
        ON child.parentID = parent.courseID
     WHERE parent.parentID IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    thanks r937, but it is giving error on sql server 2005. 'concat function requires 2 arguments'

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but you posted in the mysql forum !!

    how was i supposed to know you were actually running sql server instead?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    168
    oh yah skipped that sorry mate. So any idea or should i move the stuff there?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you have a copy of Books OnLine (a.k.a. BOL or "da manual")

    look up "string concatenation"

    it does not use the CONCAT function but rather a 1-character operator

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2009
    Posts
    168
    you mean the sql manual? Am not sure I have it. But normally, the '+' operator is used for concatenation right. So I just replace your above sql :

    SELECT parent.name + child.name AS dropdown
    FROM Courses AS parent
    INNER
    JOIN Courses AS child
    ON child.parentID = parent.courseID
    WHERE parent.parentID IS NULL

    you mean like this?

  8. #8
    Join Date
    Jul 2009
    Posts
    168
    Thanks, yes that worked but is giving me only 2 results where its NULL. I would like to get all the courses' name. Any additional query I need to add? Thanks.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kpeeroo View Post
    you mean like this?
    yup, except you forgot the space and parentheses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2009
    Posts
    168
    Thanks. But as I said I have data in the courses' name like this:

    - Bachelor of Public Administration
    - Bachelor of Business Administration
    - Bachelor of Commerce
    - Marketing Management
    - Human Resource Management

    MM, HRM are children to Bachelor of Commerce and they are in the resultset whereas the other two don't come up from the above query. Any idea?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kpeeroo View Post
    Any idea?
    try it with LEFT OUTER JOIN instead of INNER JOIN

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2009
    Posts
    168
    Nops, it is giving me 4 results but 2 of them r 'NULL'

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    definitely sounds like a data problem then

    how about creating a test case for us?

    i can set up the table on my own --
    Code:
    CREATE TABLE Courses
    ( courseID INTEGER NOT NULL PRIMARY KEY 
    , name VARCHAR(99) NOT NULL
    , parentID INTEGER NULL
    );
    now how about if you provide the data, using INSERT statements...
    Code:
    INSERT INTO Courses VALUES ( ... , ... , ... );
    INSERT INTO Courses VALUES ( ... , ... , ... );
    INSERT INTO Courses VALUES ( ... , ... , ... );
    INSERT INTO Courses VALUES ( ... , ... , ... );
    INSERT INTO Courses VALUES ( ... , ... , ... );
    if you investigate your data, and place it into these INSERT statements, then i can run them and then investigate further why you're not getting the results you're supposed to be getting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jul 2009
    Posts
    168
    ok thanks a lot r937. Cool of you. Here it goes:

    INSERT INTO Courses VALUES ( Bachelor of Commerce , NULL , 3 );
    INSERT INTO Courses VALUES ( Bachelor of Public Administration , NULL , 1 );
    INSERT INTO Courses VALUES ( Bachelor of Business Administration, NULL , 2 );
    INSERT INTO Courses VALUES ( Marketing Management , 1 , 3 );
    INSERT INTO Courses VALUES ( Human Resource Management , 1 , 3 );

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, yes, i see now, i forgot about the NULLs wiping out the concatenation

    observe how this query produces 4 rows, but you can't see two of them!! --
    Code:
    SELECT parent.name + 
           ' (' + child.name + ')' AS dropdown
      FROM Courses AS parent
    LEFT OUTER
      JOIN Courses AS child
        ON child.parentID = parent.courseID
     WHERE parent.parentID IS NULL
    
    dropdown                                                     
    ------------------------------------------------------------ 
    Bachelor of Commerce (Marketing Management)
    Bachelor of Commerce (Human Resource Management)
    
    
    
    4 row(s)
    with a small change, we make those other two rows show up --
    Code:
    SELECT parent.name + 
           COALESCE(' ('+child.name+')','') AS dropdown
      FROM Courses AS parent
    LEFT OUTER
      JOIN Courses AS child
        ON child.parentID = parent.courseID
     WHERE parent.parentID IS NULL
    
    dropdown                                                     
    ------------------------------------------------------------ 
    Bachelor of Commerce (Marketing Management)
    Bachelor of Commerce (Human Resource Management)
    Bachelor of Public Administration
    Bachelor of Business Administration
    
    4 row(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •