MuhammadNe MuhammadNe - 6 months ago 13
MySQL Question

MySQL CROSS JOIN : Every Derived Table Must Have Its Own Alias

I am trying to make a CROSS JOIN between "Selected" columns in two tables,
For example : Table 1 (a, b, c) -- Table 2 (a, b, d)
I want to select

a
and
b
from each table and then join them, but I keep getting this error :

Every derived table must have its own alias


I know this is because I need to name any derived table, but I still can't figure what's the problem and how to fix it even after searching online. This is the query :

SELECT (x.targetNumber, x.name, x.lat, x.lng)
FROM ((SELECT (u.name, u.targetNumber, u.password)
FROM Users AS u WHERE 'target' = u.type)
CROSS JOIN
(SELECT (l.targetNumber, l.password, l.lat, l.lng) FROM Location AS l)
WHERE (u.targetNumber = l.targetNumber AND u.password = l.password )) AS x;

Answer

So far it seems your query is supposed to be this:

select u.targetnumber, u.name, l.lat, l.lng
from users u
join location l on l.targetNumber = u.targetNumber and l.password = u.password
where u.type = 'target';

only that you want to apply some tricks to force the DBMS to follow some execution plan that you consider best. In doing so you make your query completely unreadable and introduce errors.

As you can see there is no cross join. You are not looking for getting all possible combinations of two data sets at all - which is what a cross join is.

And anyway this is not how SQL works. You are to write the query straigh-forward telling the DBMS what to do. The DBMS decides then how to do it.