get distinct city from the table where city column is not available

[hozyali]

Hi,

My issue is like this.

I have 3 tables
tblEvents
tblVenues
tblCities

tblEvents is the which will list the events on the page, tblEvents has a field name IDvenue (has the unique ID of the venue)

tblVenues table has the actual venue record in there, tblVenues has a field name IDcity (has the unique ID of the city

tblCities has the actual City record

Now, I need to put a drop down in sidebar say, LIST BY CITIES.
This drop down should have the distinct City Name in it. It should only list the City which has at least 1 event in it.

My main problem is the tblEvents doesn't have a direct connection with City. It has the Venue field and tblVenues has a City field, so how do I create a sql query which would list the actual DISTINCT cities considering they have at least 1 event in it?

Please help.

Thanks

[Readie]

THe MySQL "INNER JOIN" will only allow rows to return that actually have matches, so something like this should work:

SELECT
  DISTINCT(c.*)
FROM
  tblCities c
INNER JOIN
  tblVenues v
ON
  c.id = v.IDcity
INNER JOIN
  tblEvents e
ON
  v.id = c.IDvenue
ORDER BY
  c.name