Balazs Bagi <[EMAIL PROTECTED]> wrote on 08/24/2005 05:10:55 AM: > Hi there, I'm kind of new to joining two tables. Please bear with me > with this simple example. I have three tables. > > Guests is a list of guests that are coming to the party. The primary > key of this table is 'id' and the foreign key that ties the guests to > the registered user of the site is 'user_id'. > > Invites is a list of inviations sent out, with a primary key of 'id' > and a foreign key of 'guest_id' that ties it in with the guest that > the invite was sent to. > > Gifts is like invites, but is a list of gifts that have been received > by the guest. Primary key is 'id' and the foreign key is once again > 'guest_id'. > > Now, if I want to run a query that pulls all of the gifts that belong > to any guests that in turn belong to the registered user, I would do > something like: > > SELECT firstname,lastname,guests.id,giftname,giftdate FROM > guests,gifts WHERE (guests.id = invites.guest_id) AND (guests.user_id > = '$user_id') > > $user_id is our session variable. > > Ok that's fine, it gives me all of the guests first names and last > names and the name of the gifts and dates they were received, but only > of the guests that the registered user is "owner" of, ie guests that > belong to that user. > > *** MY PROBLEM ***** > > What kind of query would I run if I want to delete a gift that belongs > to the registered user? Currently, the only way to determine if a > gift belongs to the registered user is to first see if the gift came > from a guest that belongs to the registered user. It's what I refer > to as the once-removed table. I need to make a binary comparison in a > situation where there are three separate tables, instead of just two. > I know this is beginner stuff, and I hope I'm articulating my problem > without being too confusing(!) but I really want to get a grasp on > this, and none of the books I have read have specific enough examples > to guide me. > > I am trying to envision something like: > > DELETE FROM gifts WHERE guests.user_id = '$user_id' AND guests.id = > gifts.guest_id AND gift.id = '2' > > Of course I get an error. Do I need to start with "DELETE FROM > gifts,guests..." but then how will it know that I'm only actually > deleting from gifts?? > > Thank you so much in advance, > B >
This is an RTFM situation (especially since you are new): According to http://dev.mysql.com/doc/mysql/en/delete.html there are two ways to make a DELETE query that uses multiple tables. I personally prefer the first form: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*] ...] FROM table_references [WHERE where_definition] In your case, you want to delete from gift (the DELETE clause) but only those record that have matching records in the guests table (the table references part of the FROM clause). Your query will resemble this (I refuse to use the comma-separated list form of making INNER JOINS): DELETE gifts FROM gifts INNER JOIN user on gifts.guest_id = guests.id WHERE user.user_id = ... ; replace the ellipsis (...) with an actual user id. Unless your user.id field is a string, you will NOT need the single quotes around that value. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
