#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3

    Combining 2 SQL Result rows


    I have a query that get's a list of information from the answers table, it checks if the specific coach that is viewing the page has records with his coach_id in the answers table, and if other coaches have answers.

    Now when there's more than 1 coach that has answers for 1 specific employee (werknemer_id) as you can see in the screenshot below, the field get's duplicated for that coach.



    I'm wondering if it's possible to combine the 2 fields into 1 field..%aybe with comma seperated values? So i don't have a duplicate name in my result?

    This is my code

    Code:
    SELECT l.id, l.naam, r.id AS revisie, r.beschrijving, w.id AS werknemer, w.voornaam, w.achternaam, a.coach_id,
    CASE WHEN a.coach_id = 3 THEN 1 ELSE 0 END AS zelf_ingevuld,
    CASE WHEN a.coach_id != 3 AND a.coach_id != 3 THEN (SELECT CONCAT(voornaam, ' ', achternaam) FROM coaches WHERE id = a.coach_id ) ELSE 0 END AS coach_ingevuld
    FROM lijsten l
    INNER JOIN revisies r ON l.id = r.lijst_id
    INNER JOIN werknemerlijsten wl ON wl.lijst_id = l.id
    INNER JOIN werknemers w ON w.id = wl.werknemer_id
    INNER JOIN klanten k ON k.id = w.klant_id
    LEFT JOIN antwoorden a ON w.id = a.werknemer_id AND r.id=a.revisie_id
    LEFT JOIN coaches c ON c.id = a.coach_id
    WHERE r.actief = 1
    GROUP BY r.id, c.id, w.id
    Thank you!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    there are a number of concerns with your existing query, but please try this and see if it gives the right results --
    Code:
    SELECT l.id
         , l.naam
         , r.id AS revisie
         , r.beschrijving
         , w.id AS werknemer
         , w.voornaam
         , w.achternaam
         , a.coach_id
         , CASE WHEN a.coach_id = 3 
                THEN 1 
                ELSE 0 
            END AS zelf_ingevuld
         , GROUP_CONCAT(
           CASE WHEN a.coach_id = 3 
                THEN 0
                ELSE CONCAT(voornaam,' ',achternaam) 
            END 
           ) AS coach_ingevuld
      FROM lijsten l
    INNER 
      JOIN revisies r 
        ON r.lijst_id = l.id
       AND r.actief = 1
    INNER 
      JOIN werknemerlijsten wl 
        ON wl.lijst_id = l.id
    INNER 
      JOIN werknemers w 
        ON w.id = wl.werknemer_id
    INNER 
      JOIN klanten k 
        ON k.id = w.klant_id
    LEFT 
      JOIN antwoorden a 
        ON a.werknemer_id = w.id
       AND a.revisie_id = r.id
    LEFT 
      JOIN coaches c 
        ON c.id = a.coach_id
    GROUP 
        BY r.id
         , c.id
         , w.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by r937
    there are a number of concerns with your existing query, but please try this and see if it gives the right results --
    Code:
    SELECT l.id
         , l.naam
         , r.id AS revisie
         , r.beschrijving
         , w.id AS werknemer
         , w.voornaam
         , w.achternaam
         , a.coach_id
         , CASE WHEN a.coach_id = 3 
                THEN 1 
                ELSE 0 
            END AS zelf_ingevuld
         , GROUP_CONCAT(
           CASE WHEN a.coach_id = 3 
                THEN 0
                ELSE CONCAT(voornaam,' ',achternaam) 
            END 
           ) AS coach_ingevuld
      FROM lijsten l
    INNER 
      JOIN revisies r 
        ON r.lijst_id = l.id
       AND r.actief = 1
    INNER 
      JOIN werknemerlijsten wl 
        ON wl.lijst_id = l.id
    INNER 
      JOIN werknemers w 
        ON w.id = wl.werknemer_id
    INNER 
      JOIN klanten k 
        ON k.id = w.klant_id
    LEFT 
      JOIN antwoorden a 
        ON a.werknemer_id = w.id
       AND a.revisie_id = r.id
    LEFT 
      JOIN coaches c 
        ON c.id = a.coach_id
    GROUP 
        BY r.id
         , c.id
         , w.id
    Thanks! I'll try that! what are the concerns with my query please? Thanks!
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Thanks for the response! The Query already looks a lot cleaner! The problem is that the values are still being shown in 2 rows.. Is it the group by at the end that needs to be changed?

  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by notflip
    Thanks! I'll try that! what are the concerns with my query please? Thanks!
    you have "hidden" some columns from the GROUP BY clause

    every column mentioned in the SELECT clause that is not operated on by an aggregate function should really also be mentioned in the GROUP BY clause

    please see http://dev.mysql.com/doc/refman/4.1/...n-columns.html
    When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo