StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL : Append two queries with same "GROUP BY ORDER BY" and same number of rows
text
Body
copied!<p>I have the following query : </p> <pre><code>SELECT q2.name, q1.countParticipants, q2.countGames FROM ( SELECT c.countryName AS name, count(p.idParticipant) AS countParticipants FROM Country c, Participant p, Game g WHERE p.fkGame = g.idGame AND c.idCountry = p.fkCOuntry AND c.countryName LIKE '%$countryName%' GROUP BY c.countryName ORDER BY c.countryName; ) AS q1 , ( SELECT c.countryName AS name, count(g.idGame) as countGames FROM Country c, Game g WHERE c.idCountry = g.fkHostCountry AND c.countryName LIKE '%$countryName%' GROUP BY c.countryName ORDER BY c.countryName) ) AS q2 GROUP BY q1.name ORDER BY q1.name </code></pre> <p>The query is supposed to return the number of participants at the Olympic Games that took place in a given <code>countryName</code> (q1) as well as the number of <strong>total</strong> games that took place in the same <code>countryName</code> (q2). It does return something, but the result is wrong.</p> <p>Instead of returning what I need (i.e. the <strong>total</strong> number of games), it seems to return the number of games that the <code>participants</code>in <code>countParticipants</code> participated to (for the given country).</p> <p>Now, either the problem comes form the data in my DB, either from my query. Can you please check it out so I know where to correct the issue ?</p> <p>Thank you very much</p> <p><strong>NOTE</strong> : The resultSet of both ot the subqueries return the same number of rows and are ordered the same way.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload