How to handle ambiguous field in my SQL

Question

SELECT TB.ID, Latitude, Longitude, 111151.29341326 * SQRT( POW( -6 - `Latitude` , 2 ) + POW( 106 - `Longitude` , 2 ) * COS( -6 * 0.017453292519943 ) * COS( `Latitude` * 0.017453292519943 ) ) AS Distance
FROM `tablebusiness` AS TB
JOIN `tablecity` AS TC ON TB.City = TC.City
JOIN `businessestag` AS BC ON BC.BusinessID = TB.ID
JOIN `businessesdistricts` AS BD ON BD.BusinessID = TB.ID
JOIN `tabledistrict` AS TD ON TD.ID = BD.District
WHERE (
`Title` LIKE '%restaurant%'
OR `Street` LIKE '%restaurant%'
OR TB.City LIKE '%restaurant%'
OR BC.Tag LIKE '%restaurant%'
OR TD.District LIKE '%restaurant%'
)
AND (
- 6.0917668133836 < `Latitude`
AND `Latitude` < - 5.9082331866164
AND 105.90823318662 < `Longitude`
AND `Longitude` < 106.09176681338
)
ORDER BY Distance
LIMIT 0, 100

This mysql went through

Then I want to look based on building too.

So I did, like someone suggested do

SELECT
  TB.ID,
  Latitude,
  Longitude,
  111151.29341326 * SQRT(POW(-6 - `TB.Latitude`, 2) + POW(106 - `TB.Longitude`, 2) * COS(-6 * 0.017453292519943) * COS(`TB.Latitude` * 0.017453292519943)) AS Distance
FROM
  `tablebusiness` AS TB
  JOIN `tablecity` AS TC
    ON TB.City = TC.City
  JOIN `businessestag` AS BC
    ON BC.BusinessID = TB.ID
  JOIN `businessesdistricts` AS BD
    ON BD.BusinessID = TB.ID
  JOIN `tabledistrict` AS TD
    ON TD.ID = BD.District
  LEFT JOIN `tablebusiness` TBuilding
    ON TBuilding.ID = TB.Building
WHERE
  (`Title` LIKE '%restaurant%' OR `Street` LIKE '%restaurant%' OR TB.City LIKE '%restaurant%' 
    OR BC.Tag LIKE '%restaurant%' OR TD.District LIKE '%restaurant%' OR TBuilding.Title LIKE '%restaurant%')
  AND (-6.0917668133836 < `TB.Latitude` AND `TB.Latitude` < -5.9082331866164 
    AND 105.90823318662 < `TB.Longitude` AND `TB.Longitude` < 106.09176681338) 
ORDER BY
  Distance
LIMIT
  0, 100

Then I got a message that Latitude fields is ambiguous.

What should I do?

I did the obvious adding TB. before latitude

explain SELECT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326 * SQRT(POW(-6 - `TB.Latitude`, 2) + POW(106 - `TB.Longitude`, 2) * COS(-6 * 0.017453292519943) * COS(`TB.Latitude` * 0.017453292519943)) AS Distance
FROM
  `tablebusiness` AS TB
  JOIN `tablecity` AS TC
    ON TB.City = TC.City
  JOIN `businessestag` AS BC
    ON BC.BusinessID = TB.ID
  JOIN `businessesdistricts` AS BD
    ON BD.BusinessID = TB.ID
  JOIN `tabledistrict` AS TD
    ON TD.ID = BD.District
  LEFT JOIN `tablebusiness` TBuilding
    ON TBuilding.ID = TB.Building
WHERE
  (`Title` LIKE '%restaurant%' OR `Street` LIKE '%restaurant%' OR TB.City LIKE '%restaurant%' 
    OR BC.Tag LIKE '%restaurant%' OR TD.District LIKE '%restaurant%' OR TBuilding.Title LIKE '%restaurant%')
  AND (-6.0917668133836 < `TB.Latitude` AND `TB.Latitude` < -5.9082331866164 
    AND 105.90823318662 < `TB.Longitude` AND `TB.Longitude` < 106.09176681338) 
ORDER BY
  Distance
LIMIT
  0, 100

And now the error is #1054 - Unknown column 'TB.Latitude' in 'field list'

Answer 1

The correct answer is the one suggested by Li0LiQ

So I don't select this as the answer.

Just for the record, the final case is and it works. Again. Thanks Li0LiQ. Your answer is the selected answer.

explain SELECT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326 * SQRT(POW(-6 - TB.Latitude, 2) + POW(106 - TB.Longitude, 2) * COS(-6 * 0.017453292519943) * COS(TB.Latitude * 0.017453292519943)) AS Distance
FROM
  `tablebusiness` AS TB
  JOIN `tablecity` AS TC
    ON TB.City = TC.City
  JOIN `businessestag` AS BC
    ON BC.BusinessID = TB.ID
  JOIN `businessesdistricts` AS BD
    ON BD.BusinessID = TB.ID
  JOIN `tabledistrict` AS TD
    ON TD.ID = BD.District
  LEFT JOIN `tablebusiness` TBuilding
    ON TBuilding.ID = TB.Building
WHERE
  (TB.Title LIKE '%restaurant%' OR TB.Street LIKE '%restaurant%' OR TB.City LIKE '%restaurant%' 
    OR BC.Tag LIKE '%restaurant%' OR TD.District LIKE '%restaurant%' OR TBuilding.Title LIKE '%restaurant%')
  AND (-6.0917668133836 < TB.Latitude AND TB.Latitude < -5.9082331866164 
    AND 105.90823318662 < TB.Longitude AND TB.Longitude < 106.09176681338) 
ORDER BY
  Distance
LIMIT
  0, 100

Answer 2

I suppose you need to prepend Latitude you are referring to with TB alias

SELECT
  TB.ID,
  TB.Latitude,
...

It may happen, that Longitude will also be ambiguous, so I'd suggest prepending it with appropriate alias as well.

Upd.:

I would additionally suggest you to remove quotes surrounding TB.Latitude and TB.Latitude as those are causing "Unknown column" error.

Answer 3

Since in the second query you are joining tablebusiness twice, it creates ambiguity.

So in order to remove the ambiguity you will have to also include the alias of the table from which you want the columns Latitude and Longitude.

The correct query would be -

SELECT TB.ID,
       TB.Latitude,
       TB.Longitude,
<everything else remains the same.>

Answer 4

Put the Alias of the table before, like TB.Latitude