0

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;
MuhammadNe
  • 674
  • 4
  • 11
  • 24
  • You need to add an alias at the end of the second line (something like `FROM Location AS l) as y`). And before the `cross join`. And maybe some other place i overlooked... – Solarflare May 27 '16 at 14:30
  • @Jocelyn it is a duplicate but still can't figure out the problem with my query – MuhammadNe May 27 '16 at 14:31
  • `"I am trying to make a cross join query in MYSQL"` - No you are not. You are trying to make an inner join which you try to obfuscate with the keywords `CROSS JOIN`. A cross join means you join tables without criteria, simply getting all combinations. But your join criteria is simply hidden in the WHERE clause. So what is it what you *really* want to achieve? (All these parentheses are confusing by the way and make it harder to spot the error, which is indeed that you didn't give an alias name to the first derived table. – Thorsten Kettner May 27 '16 at 14:45
  • The EXISTS clauses, too, look very dubious. You are comparing columns with strings? But where do you relate the EXISTS clauses to the record of the outer query then? I suggest you ask a new question telling what you want your query to do, so people can show you a correct query for the task. – Thorsten Kettner May 27 '16 at 14:52
  • @ThorstenKettner Does the code look better now? I am trying to make a cross joint between "selected" columns in 2 tables. – MuhammadNe May 27 '16 at 14:59
  • No. Still your derived tables have no alias names. Still all those parentheses are an obstacle to the reader. Still you "cross join" only to try to inner-join your cross join by applying a WHERE clause later. Maybe it would be best you write your query without trying to force the DBMS to execute it in a special way. Write the query as simple as possible. The DBMS optimizer should be able to find the optimal execution plan. – Thorsten Kettner May 27 '16 at 15:22
  • First off, remove all the parenthesis around the column definitions in the select statements. They do nothing but make you query harder to read. Second, every time you close the parenthesis around a subselect, you need to IMMEDIATELY follow that parenthesis with the alias. – Mike Brant May 27 '16 at 15:39

2 Answers2

2

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.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thank you for the answer, it did work this way, the parentheses were making a problem, and I wrote a wrong syntax to join tables. However I will still use it with 'cross join' and not 'join' because I actually do want to get all the combinations and not just joining two tables. – MuhammadNe May 27 '16 at 15:53
1

The short answer is to move the closing parentheses from right after FROM Location AS l) to just in front of the as and remove the alias from the select list of the subquery:

...(SELECT targetNumber, password, lat, lng FROM Location) AS l...

Slightly longer answer: I do not even understand why you have subqueries in the from clause at all. You can simply join these 2 tables.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • It made a syntax error, did you check it?, The reason I make it like that is because I don't want to join two huge tables, only what I need from them, which will make the search faster. – MuhammadNe May 27 '16 at 14:38
  • sql is not going to retrieve all rows from a table just because it is part of a join. And I have no idea what query you tried or what error message you got, so I cannot possibly comment on any subsequent syntax error you got. – Shadow May 27 '16 at 14:52