homepage Welcome to WebmasterWorld Guest from 54.204.94.228
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

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




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

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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved