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

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3

    Small Survey db with Revisions problem


    Hi,

    I have a problem with a small database,
    problem is described in the image



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

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    yes it's a correct structure
    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
    yes it's a correct structure
    Thanks! I'm doing a (imo difficult) query to retrieve the 'employees' that do not have any record in the 'answers' table for that specific list and that specific revision..

    I came out on the following code ( with 99% help of someone )

    Code:
    SELECT
    	l.id, 
    	l.naam, 
    	r.id as revisie, 
    	r.beschrijving as revisiebeschrijving,
    	'Al afgelegd'
    	FROM lijsten l
    	JOIN revisies r ON r.lijst_id = l.id
    	JOIN werknemerlijsten wl ON wl.lijst_id = l.id
    	WHERE wl.werknemer_id = :id AND r.actief = 1
    	AND r.id IN 
    	(
    		SELECT id from revisies r2
    		JOIN antwoorden a2 ON a.revisie_id = r2.id
    		WHERE a2.werknemer_id = wl.werknemer_id
    	)
    	
    UNION
    
    SELECT
    	l.id, 
    	l.naam, 
    	r.id as revisie, 
    	r.beschrijving as revisiebeschrijving,
    	'Nog niet afgelegd'
    	FROM lijsten l
    	JOIN revisies r ON r.lijst_id = l.id
    	JOIN werknemerlijsten wl ON wl.lijst_id = l.id
    	WHERE wl.werknemer_id = :id AND r.actief = 1
    	AND r.id NOT IN 
    	(
    		SELECT id from revisies r2
    		JOIN antwoorden a2 ON a.revisie_id = r2.id
    		WHERE a2.werknemer_id = wl.werknemer_id
    	)
    now i'm wondering is there a shorter, or better practice of doing this?

    Thanks!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by notflip
    now i'm wondering is there a shorter, or better practice of doing this?
    yes, with a left outer join

    your query references an "a" table but you have only an "a2" table in your subqueries

    could you please explain what those two subqueries are supposed to be doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by r937
    yes, with a left outer join

    your query references an "a" table but you have only an "a2" table in your subqueries

    could you please explain what those two subqueries are supposed to be doing
    Thanks for the response.. I'm still trying to understand this query.. Do you have an example of a similar query using the left outer join maybe? I'm trying to understand. Thanks!
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by notflip
    Do you have an example of a similar query using the left outer join maybe?
    here's one --
    Code:
    SELECT l.id
         , l.naam
         , r.id as revisie
         , r.beschrijving as revisiebeschrijving
         , CASE WHEN a.werknemer_id IS NULL
                THEN 'Nog niet afgelegd'
                ELSE 'Al afgelegd' END AS result
      FROM werknemerlijsten AS wl 
    INNER  
      JOIN lijsten AS l
        ON l.id =  wl.lijst_id 
    INNER
      JOIN revisies AS r 
        ON r.lijst_id = l.id
       AND r.actief = 1
    LEFT OUTER
      JOIN antwoorden AS a 
        ON a.revisie_id = r.id
       AND a.werknemer_id = wl.werknemer_id
     WHERE wl.werknemer_id = :id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by r937
    here's one --
    Code:
    SELECT l.id
         , l.naam
         , r.id as revisie
         , r.beschrijving as revisiebeschrijving
         , CASE WHEN a.werknemer_id IS NULL
                THEN 'Nog niet afgelegd'
                ELSE 'Al afgelegd' END AS result
      FROM werknemerlijsten AS wl 
    INNER  
      JOIN lijsten AS l
        ON l.id =  wl.lijst_id 
    INNER
      JOIN revisies AS r 
        ON r.lijst_id = l.id
       AND r.actief = 1
    LEFT OUTER
      JOIN antwoorden AS a 
        ON a.revisie_id = r.id
       AND a.werknemer_id = wl.werknemer_id
     WHERE wl.werknemer_id = :id
    Very nice! And understandable query, I got some help on Stackoverflow also, but seeing your example, it's the clearest in my opinion? Most understandable at least!

    http://stackoverflow.com/questions/1...is-small-query
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by notflip
    Very nice! And understandable query, I got some help on Stackoverflow also, but seeing your example, it's the clearest in my opinion? Most understandable at least!

    http://stackoverflow.com/questions/1...is-small-query
    There is one problem with your code! When the user answers 3 questions it shows 3 times the name of the list

    Do i need a group by to counter the CASE WHEN ... ?

    Thanks!

IMN logo majestic logo threadwatch logo seochat tools logo