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

    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0

    Subquery returns more than 1 row - Complex Query


    Hello All, First time poster to this forum.

    Whilst attempting to execute an SQL Query I receive the dreaded: Subquery returns more than 1 row. I understand that MySQL is telling me more than 1 row has been returned.
    FYI I am querying Kayako Fusion 4.30.xx and some of the statements are used to determine and select Custom Fields from their schema.

    I am trying to produce a report showing the following fields:

    TicketMask ID, Member Company, Member Name, Chargeable, Subject, Issue Type, Time Worked

    Here is the current query:

    Code:
    SELECT swtickets.ticketmaskid AS `Ticket Mask ID`, ( SELECT swcustomfieldvalues.fieldvalue FROM swcustomfieldvalues, swcustomfields WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid AND swtickets.ticketid = swcustomfieldvalues.typeid AND swcustomfields.title = "Member Company" ) AS `Member Company`, ( SELECT swcustomfieldvalues.fieldvalue FROM swcustomfieldvalues, swcustomfields WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid AND swtickets.ticketid = swcustomfieldvalues.typeid AND swcustomfields.title = "Member Name" ) AS `Member Name`, ( SELECT swcustomfieldvalues.fieldvalue FROM swcustomfieldvalues, swcustomfields WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid AND swtickets.ticketid = swcustomfieldvalues.typeid AND swcustomfields.title = "Chargeable" ) AS Chargeable, swtickets.`subject` AS `Subject`, ( SELECT swcustomfieldvalues.fieldvalue FROM swcustomfieldvalues, swcustomfields WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid AND swtickets.ticketid = swcustomfieldvalues.typeid AND swcustomfields.title = "Issue Type" ) AS `Issue Type` FROM swtickets RIGHT JOIN swusers ON swtickets.userid = swusers.userid
    The Subquery Error occurs, I believe, in the last Subquery Select for the "Issue Type" - and initially was a problem because there ARE 2 Custom Field Titles of ''Issue Type'' - however they are each in separate Groups.
    To avoid that problem I chose, probably wrongly, to do the following:

    Code:
    ( SELECT swcustomfieldvalues.fieldvalue FROM swcustomfieldgroups, swcustomfieldvalues, swcustomfields, swtickets WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid AND swtickets.ticketid = swcustomfieldvalues.typeid AND swcustomfields.title = "Issue Type" AND swcustomfieldvalues.customfieldid = 30 AND swcustomfieldgroups.customfieldgroupid = 10 ) AS `Issue`
    Note the addition of the swcustomfieldvalues.customfieldid = 30 and swcustomfieldgroups.customfieldgroupid = 10 in theory allowing me to select both the correct Custom Field ID (30) and the correct Custom Field Group (for the Issue Type) of (10).

    Please, I am in desperate need of any assistance, advice, or suggestions. Maybe I need to re-think the Query.. I'm at a loss.

    Thank you kindly for your assistance,

    Edward.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    you will help yourself immensely in your future endeavours with sql by learning to format your queries with line breaks and indentation

    here's your first query --
    Code:
    SELECT swtickets.ticketmaskid AS `Ticket Mask ID`
         , ( SELECT swcustomfieldvalues.fieldvalue 
               FROM swcustomfieldvalues
                  , swcustomfields 
              WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid 
                AND swtickets.ticketid = swcustomfieldvalues.typeid 
                AND swcustomfields.title = "Member Company" ) AS `Member Company`
         , ( SELECT swcustomfieldvalues.fieldvalue 
               FROM swcustomfieldvalues
                  , swcustomfields 
              WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid 
                AND swtickets.ticketid = swcustomfieldvalues.typeid 
                AND swcustomfields.title = "Member Name" ) AS `Member Name`
         , ( SELECT swcustomfieldvalues.fieldvalue 
               FROM swcustomfieldvalues
                  , swcustomfields 
              WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid 
                AND swtickets.ticketid = swcustomfieldvalues.typeid 
                AND swcustomfields.title = "Chargeable" ) AS Chargeable
         , swtickets.`subject` AS `Subject`
         , ( SELECT swcustomfieldvalues.fieldvalue 
               FROM swcustomfieldvalues
                  , swcustomfields 
              WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid 
                AND swtickets.ticketid = swcustomfieldvalues.typeid 
                AND swcustomfields.title = "Issue Type" ) AS `Issue Type` 
      FROM swtickets 
    RIGHT 
      JOIN swusers 
        ON swtickets.userid = swusers.userid
    and here's your second --
    Code:
    ( SELECT swcustomfieldvalues.fieldvalue 
        FROM swcustomfieldgroups
           , swcustomfieldvalues
           , swcustomfields
           , swtickets 
       WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid 
         AND swtickets.ticketid = swcustomfieldvalues.typeid 
         AND swcustomfields.title = "Issue Type" 
         AND swcustomfieldvalues.customfieldid = 30 
         AND swcustomfieldgroups.customfieldgroupid = 10 ) AS `Issue`
    you never did really pose an actual question for us to help you with

    in your first query, what's the swusers table for? you're not actually using any of its columns

    in your second query, does removing the parentheses and AS `Issue` help?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    you will help yourself immensely in your future endeavours with sql by learning to format your queries with line breaks and indentation

    here's your first query --
    Code:
    SELECT swtickets.ticketmaskid AS `Ticket Mask ID`
         , ( SELECT swcustomfieldvalues.fieldvalue 
               FROM swcustomfieldvalues
                  , swcustomfields 
              WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid 
                AND swtickets.ticketid = swcustomfieldvalues.typeid 
                AND swcustomfields.title = "Member Company" ) AS `Member Company`
         , ( SELECT swcustomfieldvalues.fieldvalue 
               FROM swcustomfieldvalues
                  , swcustomfields 
              WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid 
                AND swtickets.ticketid = swcustomfieldvalues.typeid 
                AND swcustomfields.title = "Member Name" ) AS `Member Name`
         , ( SELECT swcustomfieldvalues.fieldvalue 
               FROM swcustomfieldvalues
                  , swcustomfields 
              WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid 
                AND swtickets.ticketid = swcustomfieldvalues.typeid 
                AND swcustomfields.title = "Chargeable" ) AS Chargeable
         , swtickets.`subject` AS `Subject`
         , ( SELECT swcustomfieldvalues.fieldvalue 
               FROM swcustomfieldvalues
                  , swcustomfields 
              WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid 
                AND swtickets.ticketid = swcustomfieldvalues.typeid 
                AND swcustomfields.title = "Issue Type" ) AS `Issue Type` 
      FROM swtickets 
    RIGHT 
      JOIN swusers 
        ON swtickets.userid = swusers.userid
    and here's your second --
    Code:
    ( SELECT swcustomfieldvalues.fieldvalue 
        FROM swcustomfieldgroups
           , swcustomfieldvalues
           , swcustomfields
           , swtickets 
       WHERE swcustomfieldvalues.customfieldid = swcustomfields.customfieldid 
         AND swtickets.ticketid = swcustomfieldvalues.typeid 
         AND swcustomfields.title = "Issue Type" 
         AND swcustomfieldvalues.customfieldid = 30 
         AND swcustomfieldgroups.customfieldgroupid = 10 ) AS `Issue`
    you never did really pose an actual question for us to help you with

    in your first query, what's the swusers table for? you're not actually using any of its columns

    in your second query, does removing the parentheses and AS `Issue` help?
    Thanks for the prompt reply and advice r937!

    1. I'm working from a legacy Query so the only reason I could see that swusers might be used is to JOIN a User to a swticket. I might be wrong and there is probably a better way.
    2. Removing parentheses and AS code generated syntax error in SELECT
    3. The overall problem I am repeatedly getting is the [Err] 1242 - Subquery returns more than 1 row Error Message, telling me that there is more than 1 row being returned.

    Here, if I may, is a simplified version of the Query :

    Code:
    SELECT
    	swtickets.ticketmaskid AS `Ticket Mask ID`,
    	(
    		SELECT
    			swcustomfieldvalues.fieldvalue
    		FROM
    			swcustomfieldvalues,
    			swcustomfields
    		WHERE
    			swcustomfieldvalues.customfieldid = swcustomfields.customfieldid
    		AND swtickets.ticketid = swcustomfieldvalues.typeid
    		AND swcustomfields.title = "Member Company"
    	) AS `Member Company`,
    	(
    		SELECT
    			swcustomfieldvalues.fieldvalue
    			
    		FROM
    			swcustomfieldgroups,
    			swcustomfieldvalues,
    			swcustomfields,
    			swtickets
    		WHERE
    			swcustomfieldvalues.customfieldid = swcustomfields.customfieldid
    		AND swtickets.ticketid = swcustomfieldvalues.typeid
    		AND swcustomfields.title = "Issue Type"
    		AND swcustomfieldvalues.customfieldid = 30
    		AND swcustomfieldgroups.customfieldgroupid = 10
    	) AS `Issue` 
    
    FROM
    	swtickets
    RIGHT JOIN swusers ON swtickets.userid = swusers.userid
    Still getting more than 1 row returned from the above, believing it relates to the Issue Type field/Title.

    Thank you,
    Edward
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,439
    Rep Power
    1688
    A simple idea would be to take each of the sub-queries and run them as a standalone query to see what they are returning. MySQL is complaining about the fact that one of them is returning more than one row in thr result and thus cannot fit those multiple rows into just own row of the 'outer query' - a bit like trying to fir a quart into a pint pot

    Comments on this post

    • edwardcox agrees : Thanks Simon
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo