Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: subquery more than 1 rows problem

    Hi all,

    I've 3 tables COMPANY, COMPANY_SOFTWARES, SOFWARES. COMPANY_SOFTWARES table holds the data of the softwares used in the companies like;

    COMPANY TABLE
    COMPANY_ID COMPANY_NAME
    ------------- ----------------
    10 My Company

    COMPANY SOFTWARES TABLE
    COMPANY_ID SOFTWARE_ID
    ------------- --------------
    10 1
    10 2
    10 3

    SOFWARES TABLE
    SOFTWARE_ID SOFTWARE_NAME
    -------------- -----------------
    1 X
    2 Y
    3 Z

    I need a result like;
    COMPANY_ID COMAPNY_NAME SOFTWARES
    ------------- ---------------- ------------
    10 My Company X, Y, Z


    I've used a query like;

    SELECT
    COMPANY_ID
    ,COMPANY_NAME
    ,(
    SELECT
    SOFTWARE_NAME
    FROM
    COMPANY_SOFTWARES COS,
    SOFTWARES S
    WHERE
    S.SOFTWARE_ID=CS.SOFTWARE_ID AND
    COS.COMPANY_ID = C.COMPANY_ID
    ) AS SOFWARE_NAMES
    FROM
    COMPANY C
    WHERE
    AND C.COMPANY_ID = 10

    But I get an error message;
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    I've read that in MySQL there is CONCAT function for the result I want but I couldn't find any solution in MSSQL server, any solutions, suggestions?

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you will need to write application code to do what you want (in this context, "application code" includes a cursor-based solution in a stored proc)
    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
  •