+ Post New Thread
Results 1 to 4 of 4
Web Development Thread, MySQL Subquery - Every derived table must have an alias in Coding and Web Development; I'd appreciate some help here, I've got this query that works in the MySQL Workbench against my MySQL Database: Code: ...
  1. #1

    Join Date
    Feb 2008
    Location
    Wiltshire
    Posts
    905
    Thank Post
    287
    Thanked 141 Times in 114 Posts
    Blog Entries
    28
    Rep Power
    42

    MySQL Subquery - Every derived table must have an alias

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

    Code:
    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:

    Code:
    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

  2. #2

    Join Date
    Mar 2011
    Location
    Bournemouth
    Posts
    280
    Thank Post
    16
    Thanked 74 Times in 64 Posts
    Rep Power
    22
    You need to give an alias to the subqeury:

    Code:
    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:

    Code:
    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
    Last edited by ChrisMiles; 24th September 2013 at 12:45 PM.

  3. Thanks to ChrisMiles from:

    FragglePete (24th September 2013)

  4. #3

    Join Date
    Feb 2008
    Location
    Wiltshire
    Posts
    905
    Thank Post
    287
    Thanked 141 Times in 114 Posts
    Blog Entries
    28
    Rep Power
    42
    However, this is a bad way to do this query. Try this instead:

    Code:
    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
    [/QUOTE]

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

    Pete

  5. #4

    Join Date
    Mar 2011
    Location
    Bournemouth
    Posts
    280
    Thank Post
    16
    Thanked 74 Times in 64 Posts
    Rep Power
    22
    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.



SHARE:
+ Post New Thread

Similar Threads

  1. [Video] If I Had An iPad This App Would Be A Must-Have!
    By DaveP in forum Jokes/Interweb Things
    Replies: 0
    Last Post: 24th March 2012, 07:46 PM
  2. Replies: 11
    Last Post: 22nd March 2007, 09:10 PM
  3. Qual now have an education web site
    By Dos_Box in forum Recommended Suppliers
    Replies: 2
    Last Post: 23rd June 2006, 01:38 PM
  4. Replies: 4
    Last Post: 7th October 2005, 11:55 AM
  5. It must have been a teacher!
    By Dos_Box in forum General Chat
    Replies: 8
    Last Post: 22nd August 2005, 04:44 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •