Alias to a join query

Question

( SELECT Vraboteni.v, Ulogi.p, Zarabotuva.honorar 
  FROM Vraboteni, Ulogi, Zarabotuva 
  WHERE Vraboteni.v = Ulogi.v 
    AND ima_uloga='sporedna' 
    AND Ulogi.p = Zarabotuva.p
) as F
JOIN
(  SELECT Vraboteni.v, Ulogi.p, Zarabotuva.honorar 
   FROM Vraboteni, Ulogi, Zarabotuva 
   WHERE Vraboteni.v = Ulogi.v 
     AND ima_uloga='glavna' 
     AND Ulogi.p = Zarabotuva.p
) as S
ON (F.honorar > S.honorar)

Can anyone tell me what is wrong with the syntax that I am using above? I'm having the same issue over multiple queries and I'm not sure I quite understand how I am supposed to assign an alias when I use a join (having the same issue when trying to assign alliases to multiple nested joins)

Answer 1

The subselects you join should be considered as a normal table or view, so imagine they are, and your select statement looks like this:

SELECT1 as F
JOIN SELECT2 as S ON (F.honorar > S.honorar)

This statement is missing essential parts, like a SELECT and FROM clause.

So fix it, if you want to join two selects, you should encapsulate them in another select, so you get:

SELECT
  S.*,
  F.*
FROM
  (SELECT ... ) AS F
  JOIN (SELECT ...) AS S ON (F.honorar > S.honorar)

Alternatively, you could get rid of the two subselects, use normal joins for all your tables, and end up with a query like this:

SELECT 
  Vraboteni.v, Ulogi.p, Zarabotuva.honorar 
FROM 
  Vraboteni AS v1
    JOIN Ulogi AS u1 ON v1.v = u1.v
    JOIN Zarabotuva AS z1 ON u1.p = z1.p
  CROSS JOIN Vraboteni AS v2 -- Not sure if you would want/need a condition here
    JOIN Ulogi AS u2 ON v2.v = u2.v
    JOIN Zarabotuva AS z2 ON u2.p = z2.p
WHERE 
  v1.ima_uloga = 'sporedna' -- Not sure if this should be v1, u1 or z1
  AND v2.ima_uloga = 'glavna'
  AND z1.honorar > z2.honorar