CODEWITHSUNDEEP
mysqlnestedinner-join
user2605793
user2605793

Reputation: 471

MySql Nested Select and inner join

I have an existing search that works fine. It is searching Table 1 (Clubs) but needs to get some data from Table 2 (Towns). To do that it does an INNER JOIN of two tables.

I am now trying to restrict the results to those within a set distance from a certain point.

Both tables have fields called latitude and longitude, but only the Towns values are populated, so I need to use those values in the search.

Several other fields use the same name in both tables, so I have to indicate which table I mean (eg. Towns.town_key).

To do the distance search I am doing a rough square box search in an inner nesting, followed by a pythagoras formula on the results of that, in an outer nesting. (I don't need the accuracy of Haversine.)

No matter what I try I keep getting errors on the fields that have the table name on the front. Unfortunately in spite of a lot of reading I do not fully understand the use of aliases etc.

Here is my search:

SELECT club_key, name, Towns.town_key, town_name, Clubs.address,
sqrt(((-32.380100 - Towns.latitude)*111.133)^2 + ((147.481400 - 
Towns.longitude)*93.853)^2) AS dist FROM 

(SELECT club_key, name, Towns.town_key, town_name, Clubs.address FROM Clubs 

INNER JOIN Towns ON Clubs.town_key = Towns.town_key WHERE type = 'football' 
AND Towns.latitude BETWEEN -32.829761 AND -31.930439 AND Towns.longitude BETWEEN 146.948951 AND 148.013849) AS T1 

WHERE dist < 50 ORDER BY 6,2

The latest error is: Could not run query: Unknown column 'Towns.town_key' in 'field list'

Before that it was objecting to the latitude and longitude fields, which are used in both the inner and outer selects.

Upvotes: 1

Views: 9588

Answers (2)

Jib
Jib

Reputation: 1

SELECT  
    T1.club_key, 
    T1.name, 
    T1.town_key, 
    T1.town_name, 
    T1.address,
    sqrt(((-32.380100 - T1.latitude)*111.133)^2 + ((147.481400 - T1.longitude)*93.853)^2) AS dist 
FROM 
(
    SELECT 
        Towns.latitude,
        Towns.longitude,
        club_key, 
        name, 
        Towns.town_key, 
        town_name, 
        Clubs.address 
    FROM Clubs 
    INNER JOIN Towns ON Clubs.town_key = Towns.town_key 
    WHERE 
        type = 'football' 
        AND Towns.latitude BETWEEN -32.829761 AND -31.930439 
        AND Towns.longitude BETWEEN 146.948951 AND 148.013849
) AS T1 
HAVING dist < 50 
ORDER BY 6,2

In the previous answer, i think the WHERE will not work, intead of that you have to use HAVING

Upvotes: 0

Tom Mac
Tom Mac

Reputation: 9853

Problem here is scope of your alias. Give this a go:

SELECT T1.club_key, T1.name, T1.town_key, T1.town_name, T1.address,T1.dist
FROM 
(
SELECT
sqrt(((-32.380100 - Towns.latitude)*111.133)^2 + ((147.481400 - 
Towns.longitude)*93.853)^2) AS dist, 
club_key, name, Towns.town_key, town_name, Clubs.address 
FROM Clubs 
INNER JOIN Towns ON Clubs.town_key = Towns.town_key 
WHERE type = 'football' 
AND Towns.latitude BETWEEN -32.829761 AND -31.930439 
AND Towns.longitude BETWEEN 146.948951 AND 148.013849
) AS T1 
WHERE T1.dist < 50 ORDER BY 6,2

You are trying to use an alias outside of the nested select i.e. Towns that references a table inside the nested select i.e. the Towns table. You can get round this by using your T1 alias.

Upvotes: 2

Related Questions