MySQL Subquery - Every derived table must have an alias

[FragglePete]

I'd appreciate some help here, I've got this query that works in the MySQL Workbench against my MySQL Database:

 

SELECT COUNT(DetentionID) AS CountOfDetentions, UPN, Forename, Surname FROM tbldetentions WHERE UPN IN (SELECT classUPN FROM tblclasses WHERE classClass LIKE '%11R2') GROUP BY UPN

 

... but when run on the web page with PHP gives the error:

 

Couldn't execute query. Every derived table must have its own alias - SELECT count(*) as c FROM (SELECT 1 as c FROM tblclasses WHERE classClass LIKE '%11R2')   GROUP BY UPN) o

 

I'm struggling with this, as I've tried putting AS in various places but still getting the same error.

 

Essentially this SQL statement is counting up the number times a UPN number appears in a table but the subquery is only selecting the UPN Numbers to check that are matched to a particular class number.

 

Help appreciated.

 

Thanks

 

Pete

[ChrisMiles]

You need to give an alias to the subqeury:

 

SELECT COUNT(DetentionID) AS CountOfDetentions, UPN, Forename, Surname FROM tbldetentions WHERE UPN IN (SELECT classUPN FROM tblclasses WHERE classClass LIKE '%11R2') UPNS GROUP BY tbldetentions.UPN

 

However, this is a bad way to do this query. Try this instead:

 

SELECT COUNT(tbldetentions.DetentionID) AS CountOfDetentions,
      tbldetentions.UPN,
      tbldetentions.Forename,
      tbldetentions.Surname
FROM tbldetentions
INNER JOIN tblclasses
ON tbldetentions.UPN = tblclasses.classUPN
WHERE (tblclasses.classClass LIKE '%11R2')
GROUP BY tbldetentions.UPN

Edited September 24, 2013 by ChrisMiles

[FragglePete]

However, this is a bad way to do this query. Try this instead:

 

SELECT COUNT(tbldetentions.DetentionID) AS CountOfDetentions,
      tbldetentions.UPN,
      tbldetentions.Forename,
      tbldetentions.Surname
FROM tbldetentions
INNER JOIN tblclasses
ON tbldetentions.UPN = tblclasses.classUPN
WHERE (tblclasses.classClass LIKE '%11R2')
GROUP BY tbldetentions.UPN

 

Thanks, that fixed it for me! Much appreciated. Wasn't trying to be too bad , first time meddling with Sub Queries so this will help me back on the Good path !!!

 

Pete

[ChrisMiles]

Didn't mean to be mean

 

In almost most cases joins are faster than subqueries. In a join RDBMS can create an execution plan that is better for your query and can predict what data should be loaded and processed to save time, unlike the subquery where it will run all the queries and load all the data to do the processing.