Jump to content


Photo

INNER JOIN problem with SQL query


  • Please log in to reply
2 replies to this topic

#1 tvdhoff

tvdhoff
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 07 August 2006 - 12:15 PM

Hi all,

This calendar page uses two database tables: 'calendar'  which holds the appointment info and which is linked using column 'location_id' with table 'locations' which holds the location details.

On the overview page I retrieve appointments using this query which works fine:
$listAppointmentsQuery="SELECT id,name,DATE_FORMAT(date,'%d-%m-%Y') AS date,time,location_name 
                                  FROM calendar INNER JOIN locations USING (location_id) 
                                  ORDER BY date DESC";

However, when a user deletes a location, the appointments that have that location are not displayed using this query. Is there a way to modify the query so that those entries are displayed (with an empty location) as well?

I'd rather not have to run through all appointments resetting their location_id when a location is deleted....

thanks!

Tim

#2 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 07 August 2006 - 12:31 PM

Try a left join.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#3 tvdhoff

tvdhoff
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 07 August 2006 - 12:36 PM

thanks, that helped.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users