SQL Query meeting condition on longitude AND latitude

Question

I am using mySQL and my table's columns are as follows:

post_id,meta_id,meta_value,meta_key

Dummy data is attached as:

I want data meeting the condition that latitude must be between 60 and 60.5 and longitude must be between 8 and 9.

I am using following where clause:

SELECT * 
FROM domenposts 
LEFT JOIN domenpostmeta ON domenposts.id=domenpostmeta.post_id 
WHERE ( domenpostmeta.meta_key = 'longitude' AND domenpostmeta.meta_value BETWEEN 8  AND 9 )
OR    ( domenpostmeta.meta_key = 'latitude'  AND domenpostmeta.meta_value BETWEEN 60 AND 60.5 )

Which is working but according to my logic there must me AND instead of OR between the conditions which results in zero rows. I need help to resolve this issue.

OUTPUT REQUIRED: All post_ids having latitude between 60 and 60.4 AND longitudes between 8 and 9.

Here is my data for table:

postid  metaid  meta_key  meta_value
1109    109 longitude   8.2135
1108    109 latitude    60.4029
1002    239 longitude   9
1080    241 latitude    70
1051    5   latitude    60
1001    239 latitude    62

Hi! Please can you [edit] your question (don't try to post details as comments, they won't format properly) to include: the DBMS you're using (MySQL, MS SQL Server, PostgreSQL, etc); a small sample of dummy data as text in the question, not an image; the full SQL you're running, not just the `WHERE` clause; the output you want, and the output you're getting instead. — IMSoP, Apr 25 '17 at 10:38
Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer — , Apr 25 '17 at 10:38
@FaizanZahid I still don't see the type of DB you're using, the dummy data as text, or actual examples of the expected and actual output. This may all seem obvious to you, but remember we're not looking over your shoulder, we know only the information you give us, so you have to help us to help you. — IMSoP, Apr 25 '17 at 10:55
You don't want one domenpostmeta row's meta_key to be both 'longitude' and 'latitude', you want one domenpostmeta row's meta_key to be one value and a different row's meta_key to be the other. Also why are you LEFT JOINing if you don't want a row back for every left argument table row? — philipxy, Apr 25 '17 at 11:05
PS That means you want a self join. Eg see [this re a similar query](http://stackoverflow.com/a/27682724/3404097) and [this re self join](http://stackoverflow.com/a/37384306/3404097). — philipxy, Apr 26 '17 at 00:28

score 0 · Answer 1 · answered Apr 25 '17 at 11:13

0

The or condition is just acting as union statement. So if you use below query

select * from domenposts where (meta_key='lattitude' and meta_value Between 60 and 64) 
union 
select * from domenposts where (meta_key='longitude' and meta_value Between 8 and 9)

Which is equivalent to your statement you wrote which is working

SELECT * FROM domenposts 
LEFT JOIN domenpostmeta ON domenposts.id=domenpostmeta.post_id 
WHERE (domenpostmeta.meta_key = 'longitude' AND domenpostmeta.meta_value BETWEEN  8  AND 9 ) OR    (  domenpostmeta.meta_key = 'latitude'  AND domenpostmeta.meta_value BETWEEN  60  AND 60.5  )

answered Apr 25 '17 at 11:13

DevelopmentIsMyPassion

3,541
4
34
60

The actual query which I want is SELECT * FROM domenposts LEFT JOIN domenpostmeta ON domenposts.id=domenpostmeta.post_id WHERE (domenpostmeta.meta_key = 'longitude' AND domenpostmeta.meta_value BETWEEN 8 AND 9 ) AND ( domenpostmeta.meta_key = 'latitude' AND domenpostmeta.meta_value BETWEEN 60 AND 60.5 ) – Faizan Zahid Apr 25 '17 at 11:17
The query that is "working" is not the query they want. The "working" query "works" in that it returns what they expect for a spec they have in mind using OR, but replacing OR with AND in the query doesn't give them the query they want, which is a query for the spec they have in mind using AND. – philipxy Apr 25 '17 at 11:19
and this query returns zero rows which is not the desired result. I want all post_ids which have latitude between 60 and 64 and longitude between 8 and 9. The or condition returns some post_ids which have latitude and longitude outside the desired range – Faizan Zahid Apr 25 '17 at 11:20
@FaizanZahid Again, please edit clarifications into your question. Also, you *don't* "actually want" the query in the comment, which you know is wrong, because it returns no rows. It would be helpful if you wrote a very clear statement describing what rows you want in terms of the columns plus your input tables and/or what rows they hold. Please make the effort to write clearly. Re a query version see [this](http://stackoverflow.com/a/33952141/3404097). – philipxy Apr 25 '17 at 11:20
To visualize the working Please visit boligbilder.no and search 'moholt'. The companies in moholt should appear only but companies from oslo also appears because the longitude of moholt and oslo have very less difference. – Faizan Zahid Apr 25 '17 at 11:22
@FaizanZahid can you make demo of sqlfiddle – DevelopmentIsMyPassion Apr 25 '17 at 11:33
@FaizanZahid if you say companies from oslo appearing then may be something wrong with your data type... what data type is your column meta_value – DevelopmentIsMyPassion Apr 25 '17 at 11:39

score 0 · Accepted Answer · answered Apr 27 '17 at 10:56

SELECT * FROM domenposts 
LEFT JOIN domenpostmeta p1 ON domenposts.id=p1.post_id 
LEFT JOIN domenpostmeta p2 ON p1.post_id = p2.post_id 
WHERE (p1.meta_key = 'longitude' AND p1.meta_value BETWEEN  8  AND 9 ) OR    (  p2.meta_key = 'latitude'  AND p2.meta_value BETWEEN  60  AND 60.5  )

This is the solution I implemented to solve my problem

SQL Query meeting condition on longitude AND latitude

2 Answers2