Welcome to WebmasterWorld Guest from 54.159.44.227

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

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

     

hozyali

7:06 pm on Dec 26, 2010 (gmt 0)

5+ Year Member



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

2:47 am on Dec 28, 2010 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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
 

Featured Threads

Hot Threads This Week

Hot Threads This Month