Mr. Robot February 2016

Every Derived Table must have it's own alias

Here is my MySQL Syntax:

SELECT f1.id
FROM
  (SELECT f1.id,
          count(*) AS c1
   FROM (friend AS f1
         JOIN friend AS f2 ON (f1.fid=f2.fid
                               AND f1.id!=f2.id
                               AND f2.id=12345
                               AND f1.id!=12345)))
WHERE c1>=
    (SELECT count(*)
     FROM friend AS f3
     WHERE f3.id=12345);

I can't seem to figure out where's the problem, I tried aliasing the inner result tables but they seem to return an error! I need guidelines.

Answers


Giorgos Betsos February 2016

Get rid of extraneous parenthesis and format your query a bit to have a cleaner look:

SELECT t.id 
FROM (SELECT f1.id,count(*) AS c1 
      FROM friend AS f1 
      JOIN friend AS f2
         ON  f1.fid=f2.fid AND 
             f1.id!=f2.id AND 
             f2.id=12345 AND 
             f1.id!=12345) AS t -- <-- Alias needed here!
WHERE c1>=(SELECT count(*) FROM friend as f3 WHERE f3.id=12345)

If you format like above, then you'll realize that the derived table of your query is missing an alias.

Also, the field selected by the outermost query, i.e. id should be prefixed using the alias of the derived table, i.e. t.

Post Status

Asked in February 2016
Viewed 3,296 times
Voted 4
Answered 1 times

Search




Leave an answer