Member Avatar for TheSassyDragon

Im trying to write a delete function for a "path" class which has data points (lat/long). It also has notes and photos along it but each of these objects have comments that also need to be deleted. Essentially everything associated with the path.

Here's my SQL attempt in the most basic

SELECT ws_paths.id as path_id, ws_path_data.path_id as data_id, 
	ws_path_notes.path_id as note_id, ws_path_photos.path_id as photo_id
	 ws_comments.object_id as comment_id

FROM ws_paths, ws_path_data, ws_path_notes, ws_path_photos, ws_comments

WHERE path_id = data_id AND
	path_id = note_id AND
	path_id = photo_id AND
	comment_id = photo_id AND
        comment_id = note_id

I feel like there could be a much better or more efficent way to do this, any advice would be greatly welcomed! Ive read everything on joins i'm just stuck on the distinctions between left, right, inner, and outter ect.

  • 2 Contributors
  • 1 Reply
  • 121 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for urtrivedi

if you want to delete things, on path_id from the related table, so best thing is create foreign keys in all related table with cascade delete option

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.