#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    1
    Rep Power
    0

    Nestled SELECT in FROM problem


    I've got this SQL-code here, and I'm pretty sure most of it will work out right. The problem is that my SELECTs in the FROMs do not have access to the alias M1 and M2. I am unsure how to rewrite the code so that it will be correct.

    Thanks in advance for any help.
    Code:
    (SELECT MAX(LC) FROM
    
    (SELECT COUNT(Y.lagId) AS LC
    FROM Arena AS A2, Match AS M2, (SELECT DISTINCT lagId FROM LagDeltagande AS LD2 WHERE LD2.lagId=M2.Id) AS Y
    
    WHERE M2.aPStad=A2.pNamn
    AND M2.aPLand=A2.pLandNamn
    AND M2.aNamn=A2.namn));
    The reason why I didn't include all the code was because it kept me from posting. It had something to do with 'akistmetspam.'
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Code:
    SELECT MAX(LC) AS max_lc
      FROM ( SELECT LD2.lagId
                  , COUNT(*) AS LC
               FROM Arena AS A2
             INNER
               JOIN Match AS M2
                 ON M2.aPStad = A2.pNamn
                AND M2.aPLand = A2.pLandNamn
                AND M2.aNamn  = A2.namn
             INNER
               JOIN LagDeltagande AS LD2 
                 ON LD2.lagId = M2.Id
             GROUP
                 BY LD2.lagId ) AS Y
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo