Jump to content

[SOLVED] How to simplify a complex query?


sevun

Recommended Posts

Table - column1, column2, column3, etc..:

event - eventID, eventLat, eventLng, profileID, categoryID

profile - profileID, profileUsername

category - categoryID, categoryName

 

The query:

 

$query = "SELECT 
`event`.`eventLat`,
`event`.`eventLng`,
`profile`.`profileUsername`,
`category`.`categoryName`,
    FROM event, profile, category
    WHERE (
        (`event`.`eventLat` <= ".$north.")
    AND (`event`.`eventLat` >= ".$south.")
    AND (`event`.`eventLng` <= ".$east.")
    AND (`event`.`eventLng` >= ".$west.")
    AND (`event`.`profileID` = `profile`.`profileID`)
    AND (`event`.`categoryID` = `category`.`categoryID`)
    )

 

The speed was okay until I added the lines:

 

AND (`event`.`profileID` = `profile`.`profileID`)
AND (`event`.`categoryID` = `category`.`categoryID`)

 

These two lines are not filtering anything, they are just replacing an ID number with a text.  Is there a query to filter the results to the set desired (e.g. just return the filtered primary key column of the 'event' database), then query the three databases for the information I want?

Link to comment
https://forums.phpfreaks.com/topic/89648-solved-how-to-simplify-a-complex-query/
Share on other sites

Using the code in the LEFT JOIN clause

 

SELECT event.eventLat, event.eventLng, profile.profileUsername, category.categoryName
FROM event
LEFT JOIN profile ON event.profileID = profile.profileID`
LEFT JOIN category ON event.categoryID = category.categoryID
WHERE ... 

 

in place of using the AND clause

 

SELECT event.eventLat, event.eventLng, profile.profileUsername, category.categoryName
FROM event, profile, category
WHERE ((event.profileID = profile.profileID)
AND (event.categoryID = category.categoryID)
AND ... 

 

worked like a champ!  I'm guessing this is how I should of done it from the beginning.  Thanks for everyone's help.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.