Display 2 queries in the same table?

Dev Shed Forums Sponsor:

November 6th, 2012, 05:23 PM

notflip

Contributing User

Join Date: Sep 2012

Posts: 148

Time spent in forums: 18 h 39 m 9 sec
Reputation Power: 1

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!

November 6th, 2012, 05:33 PM

requinix

Still alive

Join Date: Mar 2007

Location: Washington, USA

Posts: 12,711

Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 5 Months 1 Week 4 Days 6 h 45 m 48 sec
Reputation Power: 8969

Send a message via Google Talk to requinix

What are the queries you're running?

__________________
Site rules and FAQ | Project Suggestions | mod_rewrite guide | PHP New User Guide and FAQ - PHP Stickies | Regex Resources

November 6th, 2012, 05:35 PM

notflip

Contributing User

Join Date: Sep 2012

Posts: 148

Time spent in forums: 18 h 39 m 9 sec
Reputation Power: 1

Quote:

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
");

November 6th, 2012, 05:44 PM

schworak

Registered User

Join Date: Jul 2004

Posts: 11

Time spent in forums: 1 h 43 m 11 sec
Reputation 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.

November 6th, 2012, 05:48 PM

schworak

Registered User

Join Date: Jul 2004

Posts: 11

Time spent in forums: 1 h 43 m 11 sec
Reputation 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"

November 6th, 2012, 05:50 PM

notflip

Contributing User

Join Date: Sep 2012

Posts: 148

Time spent in forums: 18 h 39 m 9 sec
Reputation Power: 1

Quote:

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!

November 6th, 2012, 06:20 PM

notflip

Contributing User

Join Date: Sep 2012

Posts: 148

Time spent in forums: 18 h 39 m 9 sec
Reputation Power: 1

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

Display 2 queries in the same table?

Developer Shed Advertisers and Affiliates