MySql Nested Select and inner join

Question

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.

Tom Mac · Accepted Answer · 2013-10-24 11:01:57Z

2

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.

edited Oct 24, 2013 at 11:01

answered Oct 24, 2013 at 10:08

Tom Mac

9,8633 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2605793 Over a year ago

What connects T1.town_key to Towns.town_key please? And T1.address to Clubs.address?

user2605793 Over a year ago

Tried it. Now get error: Could not run query: Unknown column 'T1.latitude' in 'field list'

Tom Mac Over a year ago

Does the latitude column exist on the Towns table? If so then the answer above should work....

user2605793 Over a year ago

Ignore that. My fault.

user2605793 Over a year ago

Hmmm. Now getting the error: Could not run query: BIGINT UNSIGNED value is out of range in '((((cast(-(32.380100) as signed) - cast(tbl.latitude as signed)) * 111.133) ^ 2) + (((cast(147.481400 as signed) - cast(tbl.longitude as signed)) * 93.853) ^ 2))' I changed it to use CAST as SIGNED but still failing.

code_fish · Accepted Answer · 2013-10-24 11:10:20Z

0

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

edited Oct 24, 2013 at 11:10

code_fish

3,4185 gold badges49 silver badges95 bronze badges

answered Oct 24, 2013 at 10:51

Jib

1

2 Comments

user2605793 Over a year ago

I tried HAVING but I still get an error Could not run query: Unknown column 'T1.latitude' in 'field list'

user2605793 Over a year ago

In the inner select do I need to say: Select Towns.latitude AS latitude so that the outer T1.latitude will work?

Collectives™ on Stack Overflow

MySql Nested Select and inner join

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related