mysql where multiple fields in

Question

I want to extract unique minlat, minlng based on city and country then want to find all id which have this pair I mean something like

select id from spots 
where (minlat,minlng) in 
    (select s.minlat, s.minlng from spots s, spot_cards sc 
    where sc.spot_id=s.id and sc.country="italy" 
    and 
        (sc.city="perugia" or sc.locality="perugia" or sc.sublocality="perugia")
    );

Structure of spots table is:

     +----+-----------+-----------+
     | id | minlat    | minlng    |
     +----+-----------+-----------+

I created spot_cards table structure as

    +---------+-------------+-------------+---------+--------+
    | spot_id | sublocality | locality    | country | city   |
    +---------+-------------+-------------+---------+--------+

by executing below query

insert into spot_cards(spot_id) 
select id from spots 
group by minlat,minlng 
order by id

Any help is appreciated.

Answer 1

I think you need something like this:

Join the spots table to get all the records you want, then join again on the spots table to get the coordinates. Do a DISTINCT to get rid of any possible duplicates.

SELECT DISTINCT s2.id from spots s
INNER JOIN spot_cards sc
ON sc.spot_id=s.id
INNER JOIN spots s2
ON s.minlat = s2.minlat AND s.minlng = s2.minlng
WHERE  sc.country="italy" 
       AND (sc.city="perugia" or sc.locality="perugia" or sc.sublocality="perugia")