none
Help with subquery: comma-delimited id list to value

    Question

  • Hello any MS SQL experts out there! please help if you can. i'm trying to run a subquery
    within a query to keep myself from having to loop over the original query on display and
    then run additional queries to get the further info. here's the setup. i have two tables:

    persons table
    column: name (varchar)
    column: vehicleids (varchar)

    vehicles table
    column: id (int pk)
    column: vehiclename (varchar)

    - The persons table is a list of peoples' names and what kind of vehicle/s they own.
    - The persons.vehicleids field is a comma-delimited list of one or more integers which correspond to the vehicles.id field.
    - The vehicles table contains a list of vehicles like car, bicycle, motorcycle, etc, distinguished by the vehicles.id field.

    The result i want returned by the query is:

    NAME - VEHICLES
    Joe Somebody - car,bicycle
    Sheila Johnson - van,pogostick,motorcycle
    John Nobody - skateboard,car

    The query i'm trying to run to get this result is:

    Code Snippet

    SELECT pe.name,
        (
            SELECT ve.vehiclename
            FROM vehicles ve
            WHERE CAST(ve.id AS VARCHAR) IN (pe.vehicleids)
        ) AS vehicles
    FROM persons pe
    ORDER BY pe.name



    It returns the persons names in the first column, but only returns vehicle names in the
    second column when there's a single id in the persons.vehicleids field. if there's more
    than one integer in the field, it returns an empty string.

    Can anyone tell me what I'm doing wrong? I do have the option of table restructuring if
    its necessary, but I'm not looking for a stored procedure solution or a temp table
    solution. Any takers? I would be in the kharmic debt of anyone providing a workable
    avenue.

    Thank you,
    Tyler
    Friday, August 17, 2007 4:05 AM

Answers

All replies

  •  

    >  persons.vehicleids field is a comma-delimited list of one or more integers

     

    Storing a comma separated list is not generally a good idea, it's inefficient and difficult to maintain. Better
    off with one id per row stored in an integer column. However, changing your query to this should work (untested)


    SELECT pe.name,
        (
            SELECT ve.vehiclename
            FROM vehicles ve
            WHERE ','+pe.vehicleids+',' LIKE '%,'+CAST(ve.id AS VARCHAR(10))+',%'
        ) AS vehicles
    FROM persons pe
    ORDER BY pe.name

    Friday, August 17, 2007 7:37 AM
  • Mark.

    Thank you for responding. When I run the query you provided, I receive this error:

    "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

    I do want the multiple values to be returned, but how can I concatenate all those values into one field like vehicles = "bike,car,motorcycle"?
    Friday, August 17, 2007 4:07 PM
  • Concatenating row values in Transact-SQL

    http://www.projectdmx.com/tsql/rowconcatenate.aspx

     

     

    AMB

    Friday, August 17, 2007 6:14 PM
    Moderator