Re: Subtables

So, you have Parties, Attendees, and PartyAttendees (at least that's the way I would break it down).

So, here's one to tell you all about the people at a party:

Select

            *
from

            Attendees
where

    	Attendee.ID IN (
    	    	select
    	    	    	Attendee.ID
    	    	from
    	    	    	PartyAttendees
    	    	where
    	    	    	Party.ID = [ID of the party you want to know about]
    	    	)

You could read the above as
    	Who are the Attendees 
    	    	That were at this Party

Similarly, to find out which parties the chap was at:

Select

            *
from

            Parties
where

      Party.ID IN (
    	    	select
    	    	      Party.ID
    	    	from
    	    	    	PartyAttendees
    	    	where
    	    	    	Attendee.ID = [ID of the Attendee you want to know about]
    	    	)

You could read this as
    	Which were the Parties
    	    	That were attended by this Attendee

To find out all the Attendees who attended a party with your chap, it's sort of a cross between the two:

Select

            *
from

            Attendees
where

    	Attendee.ID IN (
    	    	select
    	    	    	Attendee.ID
    	    	from
    	    	    	PartyAttendees
    	    	where
    	    	    	Party.ID (
    	    	    	    	select
    	    	    	    	      Party.ID
    	    	    	    	from
    	    	    	    	    	PartyAttendees
    	    	    	    	where
    	    	    	    	    	Attendee.ID = [ID of the Attendee]
    	    	    	    	)
    	    	)

And this reads as
    	Who were the Attendees
    	    	Who were at the Parties
    	    	    	Which were attended by this Attendee

You could do all of this with joins, as well, but I think this is easier to read, as you can see how easily it maps to the plain english.

Good luck SexyMF
- Chris

sexymf_at_flashmail.com (SexyMF) wrote in <3a54bab1.120575490_at_pubnews.netcom.net.uk>:

>Hi,
>Ok I still don't get it. I've posted about this over here before, but I
>still don't see how so...... - how do I do subtables? Say we have a
>number of parties being held, and a certain total no. of people going to
>them - for each party, we have info on where it was held, theme, etc.,
>and for each of the people we know their names, ages, sex etc. Now
>obviously lots of people were at one single party, however each person
>could have gone to > 1 (ie. a many to many relationship). We'd like to
>be able to search to see if a particular chap was there, given a certain
>party, and look up his age and so forth, and also, given a certain guy,
>we'd like to see which are the parties he attended and who was at each
>ONE and so forth........ how do I do this?
>
>Thanks for all your help,
>
>SMF.
>
>
Received on Thu Jan 04 2001 - 21:02:35 CET