CODEWITHSUNDEEP
mysql
Adham
Adham

Reputation: 64844

what is the wrong in this MySQL command?

I have the following query in MySQL

SELECT  *,(select lat from node where id=node_id)as lat,(select lng from node where id=node_id)as lng from tag,node WHERE 1 limit 5

but i get this error message ..

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

what's the problem ??

Upvotes: 0

Views: 78

Answers (4)

Alex
Alex

Reputation: 14618

You might want to:

  • Specify a valid WHERE clause, which would contain a boolean expression, or don't use one at all.
  • Use full field names (lat.id, lat.node_id? ) or something
  • Use unique aliases for tables (you are using 2 different sets of node twice in a single scope - inside the subqueries)

Generally - write a more comprehensive, and specific query, which allows no ambiguity and no trouble.

i want to retrieve data from tag table where it has node_id column, and i want to retrieve the corresponding lat,lng columns from node table

SELECT
    tag.*,
    node.lat,
    node.lng
FROM
    tag
JOIN node ON tag.node_id=node.id
LIMIT 5

Upvotes: 2

HLGEM
HLGEM

Reputation: 96562

Ok there are so many poor coding techniques in that I'm not sure where to start.

First, did you mean to have a cross join becasue that is what you have. It is a very bad practice to use implicit joins in writing sql code. IT leads to mistakes and it is harder to maintain. You would not write C# or Java code that had been replaced with someting better in 1992 would you?

select * is a very bad choice. Always explicitly define what columns you need.

What are you tyring to get at with WHERE 1? That doesn't make sense.

ANd generally correlated subqueries are bad. Why are you not just doing an inner join and then selecting the fields?

Upvotes: 1

Mchl
Mchl

Reputation: 62377

Try:

SELECT  tag.*, node.*,
  (select lat from node where id=node_id)as lat,
  (select lng from node where id=node_id)as lng 
from tag,node 
WHERE 1 limit 5

this however will be very inefficient, as it uses two correlated subqueries and no JOIN condition. Perhaps you could explain what you want to achieve, and we could improve this query?

Upvotes: 1

Dlongnecker
Dlongnecker

Reputation: 3047

I think what you're looking for is this. This code is equivalent to "Show me the first 5 rows from tag and the corresponding columns 'lat' and 'lng' from node where node_id in tag equals node_id in node"

SELECT tag.*, node.lat, node.lng
FROM tag JOIN node ON tag.node_id=node.node_id
LIMIT 1,5

Upvotes: 2

Related Questions