MySQL: campi con stesso identificativo

[Progeny]

Salve, ho una query del genere:

$mysql->query("SELECT visits.uid,
                      DATE_FORMAT(visits.date, '%e %M %Y - %T') AS `fdate`,
                      visits.target,
                      users.username,
                      users.sex
               FROM visits
               LEFT JOIN users ON users.id = visits.uid
               WHERE visits.target = '$uid'
               ORDER BY visits.date DESC"); 

e qui va tutto ok, però ho bisogno di fare due left join, quando aggiungo alla query:
"LEFT JOIN `cities` ON cities.id = profile.city" (più altra roba nel select-statement), ricevo un errore, perchè la tabella profile contiene un campo uid, questo perchè anche la tabella visits possiede un campo uid...

come posso risolvere il problema?

[}gu|do[z]{®©]

se usi la dot notation e sono tutte tabelle diverse (e non più istanze della stessa tabella) non vedo che problema possa esserci.... il problema sorge appunto se usi più istanze della stessa tabella, ma lo risolvi rinominandone una con "AS"....

[Progeny]

$mysql->query("SELECT visits.uid,
                      DATE_FORMAT(visits.date, '%e %M %Y - %T') AS `fdate`,
                      visits.target,
                      users.username,
                      users.sex,
                      cities.description AS `city`
               FROM visits, profile AS `profile_tmp`
               LEFT JOIN users ON users.id = visits.uid
               LEFT JOIN `cities` ON cities.id = profile_tmp.city
               WHERE visits.target = '$uid' AND visits.date > DATE_ADD(CURDATE(), INTERVAL -1 DAY)
               ORDER BY visits.date DESC"); 

Database error: Invalid SQL: SELECT visits.uid, DATE_FORMAT(visits.date, '%e %M %Y - %T') AS `fdate`, visits.target, users.username, users.sex, cities.description AS `city` FROM visits, profile AS `profile_tmp` LEFT JOIN users ON users.id = visits.uid LEFT JOIN `cities` ON cities.id = profile_tmp.city WHERE visits.target = '1' AND visits.date > DATE_ADD(CURDATE(), INTERVAL -1 DAY) ORDER BY visits.date DESC
MySQL Error: 1054 (Unknown column 'visits.uid' in 'on clause')
Session stopped.

non va

[Progeny]

risolto, santa la molecola della caffeina :rollo:

$mysql->query("SELECT visits.uid,
                      DATE_FORMAT(visits.date, '%e %M %Y - %T') AS `fdate`,
                      DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(users.birthday)), '%Y')+0 AS `age`,
                      visits.target,
                      users.username,
                      users.sex,
                      profile.city AS `city_id`,
                      cities.description AS `city`
               FROM visits
               LEFT JOIN users ON users.id = visits.uid
               LEFT JOIN profile ON profile.uid = users.id
               LEFT JOIN cities ON cities.id = profile.city
               WHERE visits.target = '$uid' AND visits.date > DATE_ADD(CURDATE(), INTERVAL -1 DAY)
               ORDER BY visits.date DESC");