Sorry, that last one was bad: Select c.*, fg.* From clients c inner join family_groups fg on c.client_family_id = fg.group_id Where c.family_group_id = ( select family_group_id from clients where client_id = #url.client_id# )
Rick Faircloth wrote: >Ok... > >Clients Table >------------------ >Client_ID >Client_Family_ID (FK for Family_Groups Table) >etc... > >Family_Groups Table >------------------------------ >Group_ID > > >There are 4 records in the Clients table...all have >the same Client_Family_ID, which is "1"... > >There is 1 record in the Family_Groups table, >which has the Family_Group_ID of "1"... > >Link is clicked, sending "Client_ID = 1"...(that client >has a Client_Family_ID FK that = "1" > >So, give me all the records where the Family_Group_ID = Client_Family_ID >and the Client_ID = 1 ('#URL.Client_ID#')... > >I think that "Client_ID = '#URL.Client_ID#' is going to >limit the records to 1... > >But how do I get all Clients that have Family_Group_ID's >that match the Family_Group_ID of the Client specified by the >'#URL.Client_ID#' ??? > >I know I've been away from coding for a couple of months...but sheesh! > >Rick > > > > > > > > > >>-----Original Message----- >>From: Ken Ferguson [mailto:[EMAIL PROTECTED] >>Sent: Monday, October 03, 2005 1:02 PM >>To: CF-Talk >>Subject: Re: Why doesn't this query return more than 1 record? >> >> >>How many records are in each table and what do they look like (just the >>columns in question). The only way I'd see you getting more than one >>record back is if there are several different records in the clients >>table with the same client_id. I'm guessing the tables look >>similar to this: >> >>CLIENTS: >>client_id >>1 >>2 >>3 >> >>client_family_id >>123 >>456 >>789 >> >>FAMILY_GROUPS >>group_id >>123 >>456 >>789 >>group_name >>smith >>james >>johnson >> >>So, when your url.client_id variable equals 1, you're going to get >> >>client_id = 1, fg.group_id = 123, fg.group_name = smith >> >>Am I missing something here???? >> >> >> >> >>>>>> Select FG.Group_ID, FG.Group_Name, >>>>>> C.Client_ID, >>>>>> C.Client_First_Name, C.Client_Middle_Name, >>>>>> >>>>>> >>>>>> >>>>>> >>>>>C.Client_Last_Name >>>>> >>>>> >>>>> >>>>> >>>>>> from Family_Groups FG, Clients C >>>>>> where FG.Group_ID = C.Client_Family_ID >>>>>> and C.Client_ID = '#URL.Client_ID#' >>>>>> >>>>>>Rick >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219943 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54