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

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2

    Display 2 queries in the same table?


    I have 2 Queries. but i want to show them in the same table. is it possible to iterate through them at the same time? pretty confused here!

    --------------------------------------------------- |
    category_name (query1) | average score (query1) |
    -----------------------------------------------------
    number of empty_questions (query2) |
    -----------------------------------------------------

    --------------------------------------------------- |
    category_name (query1) | average score (query1) |
    -----------------------------------------------------
    number of empty_questions (query2) |
    -----------------------------------------------------

    Thanks!
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,053
    Rep Power
    9398
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    Originally Posted by requinix
    What are the queries you're running?
    They're pretty complicated.. to me..

    Code:
    $gemiddeldePerCategorie = mysql_query("
    	SELECT ROUND(AVG(antwoord_naam),1) * 10 as average, categorieen.categorie_naam
    	FROM antwoorden
    	INNER JOIN vragen ON antwoorden.vraag_id = vragen.vraag_id
    	INNER JOIN categorieen ON categorieen.categorie_id = vragen.categorie_id
    	WHERE antwoorden.werknemer_id = '$werknemerID'
    	GROUP BY categorieen.categorie_id");
    Code:
    $nietIngevuld = mysql_query("
    	SELECT COUNT(antwoorden.vraag_id) as niet_ingevuld, categorieen.categorie_naam
    	FROM antwoorden
    	INNER JOIN vragen ON antwoorden.vraag_id = vragen.vraag_id
    	INNER JOIN categorieen ON categorieen.categorie_id = vragen.categorie_id
    	WHERE antwoorden.werknemer_id = '$werknemerID' AND antwoord_naam = 0
    	GROUP BY categorieen.categorie_id
    ");
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    11
    Rep Power
    0
    It really looks like you can combine those two queries into a single query to get multiple columns back. That would be the best way to keep your data synchronized to each other.

    If you really need two queries, get a key from each of them that could be used to link them together row by row, then perform a larger query with each smaller query as a subquery in the from and again join them together to get a single dataset back.

    Otherwise, you would need some sort of key and compare step by step through the data moving forward on one query or the other (or both) and that is rather painful.

    So if possible, use a JOIN to get your data into a single recordset.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    11
    Rep Power
    0
    Code:
    SELECT 
         sum(case when antwoord_naam = 0 then 1 else 0 end) as niet_ingevuld,
         ROUND(AVG(antwoord_naam),1) * 10 as average, 
         categorieen.categorie_naam
    FROM antwoorden
    INNER JOIN vragen ON antwoorden.vraag_id = vragen.vraag_id
    INNER JOIN categorieen ON categorieen.categorie_id = vragen.categorie_id
    WHERE antwoorden.werknemer_id = '$werknemerID'
    GROUP BY categorieen.categorie_id"
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    Originally Posted by schworak
    Code:
    SELECT 
         sum(case when antwoord_naam = 0 then 1 else 0 end) as niet_ingevuld,
         ROUND(AVG(antwoord_naam),1) * 10 as average, 
         categorieen.categorie_naam
    FROM antwoorden
    INNER JOIN vragen ON antwoorden.vraag_id = vragen.vraag_id
    INNER JOIN categorieen ON categorieen.categorie_id = vragen.categorie_id
    WHERE antwoorden.werknemer_id = '$werknemerID'
    GROUP BY categorieen.categorie_id"
    Nice! Never heard of the CASE function. I'll look into that! didn't know it was ever possible to get this Query into 1! Thank you so much!
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    There's one more problem I'm trying to solve. But i can't seem to get a WHERE clause inside this one? The line in bold has to be higher then 0 to be counted but

    WHERE antwoord_naam > 0 can't be inserted because it's already a subquery?

    Code:
    SELECT 
         sum(case when antwoord_naam = 0 then 1 else 0 end) as niet_ingevuld,
    COUNT(antwoorden.vraag_id) as aantal,
         ROUND(AVG(antwoord_naam),1) * 10 as average, 
         categorieen.categorie_naam
    FROM antwoorden
    INNER JOIN vragen ON antwoorden.vraag_id = vragen.vraag_id
    INNER JOIN categorieen ON categorieen.categorie_id = vragen.categorie_id
    WHERE antwoorden.werknemer_id = 14
    GROUP BY categorieen.categorie_id

IMN logo majestic logo threadwatch logo seochat tools logo