Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unanswered: issues w/ group_concat- it truncates the data

    Hello,

    I'm trying to use a group_concat on select. For some reason, it trunscates my data (it cuts off at row 43 when I'm supposed to have 92 rows of data). When I take off the group_concat and only use SELECT me.SUBJECT, all my 92 rows of data show.

    Can anyone advice? Thank you.



    SELECT CONCAT("'",GROUP_CONCAT(me.SUBJECT SEPARATOR "','"),"'")
    FROM myaccountmyevents AS me
    LEFT JOIN auth AS a ON me.createdBy=a.username
    LEFT JOIN eventcalendarimages AS ei ON me.category=ei.eventCalendarImageId
    LEFT JOIN businessurlalias AS bua ON bua.calendarEventId=me.calendarEventId
    LEFT JOIN businessbios AS bb ON bb.businessOptionsBiosId=bua.businessOptionsBiosId
    WHERE me.OWNER=1 AND startDateFull > '2011-05-13' AND whichShare > 0
    AND (me.city='Aurora'
    OR me.city='Broomfield'
    OR me.city='Lakewood'
    OR me.city='Littleton'
    OR me.city='Thorton'
    OR me.city='Westminster'
    OR me.city='Arvada'
    OR me.city='Wheat Ridge'
    OR me.city='Commerce City'
    OR me.city='Greeley'
    OR me.city='Brighton'
    OR me.city='Lousville'
    OR me.city='Lafayette'
    OR me.city='Centennial'
    OR me.city='Greenwood Village'
    OR me.city='Bow Mar'
    OR me.city='Columbine Valley'
    OR me.city='Glendale'
    OR me.city='Mountain View'
    OR me.city='Northglenn'
    OR me.city='Denver'
    OR me.city='Castle Rock'
    OR me.city='Sheridan'
    OR me.city='koko')
    OR me.city='Highlands Ranch' AND (me.state='Colorado')
    ORDER BY me.startDateFull;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you messed up your ANDs and ORs

    WHERE me.OWNER=1 AND startDateFull > '2011-05-13' AND whichShare > 0
    AND (me.city='Aurora'
    OR me.city='Broomfield'
    OR me.city='Lakewood'
    OR me.city='Littleton'
    OR me.city='Thorton'
    OR me.city='Westminster'
    OR me.city='Arvada'
    OR me.city='Wheat Ridge'
    OR me.city='Commerce City'
    OR me.city='Greeley'
    OR me.city='Brighton'
    OR me.city='Lousville'
    OR me.city='Lafayette'
    OR me.city='Centennial'
    OR me.city='Greenwood Village'
    OR me.city='Bow Mar'
    OR me.city='Columbine Valley'
    OR me.city='Glendale'
    OR me.city='Mountain View'
    OR me.city='Northglenn'
    OR me.city='Denver'
    OR me.city='Castle Rock'
    OR me.city='Sheridan'
    OR me.city='koko')

    OR me.city='Highlands Ranch' AND (me.state='Colorado')

    you have two compound conditions -- the red one and the blue one

    if either is true, that row is kept
    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
  •