Hrm, dunno. Lemme check with her and see what she says. Maybe this is why I've only gotten one response so far....heh
Ben Nadel wrote: > Ray, > > I am not understanding what you want the result set to look like? You can't > really have grouped items within a single row... If you are grouping items, > you either have to get an aggregate of the grouped rows (ex. SUM, COUNT) or > you have to get multiple items (potentially) of the left join table. > > If you could write out pseudo table code (like the tables you had below), > what would you want the result set to look like?? > > ....................... > Ben Nadel > www.bennadel.com > -----Original Message----- > From: Ray Champagne [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 27, 2006 1:28 PM > To: CF-Talk > Subject: SQL join help > > I'm posting this for a co-worker, I told her you guys were the bomb. > Prove me right! :) I can ask her any questions that were left out here. > BTW, this is a SQL Server 2K database. > > essentially, i have 3 tables: table1, table2 and tablepivot which relates > the titles in table1 to any variety of titles in table2. > > table1: > > id title desc > =========================================== > 1 title one the first description > 2 title two the second description > 3 title three the third description > > table2: > > id titles > ================== > 1 title1 > 2 title2 > 3 title3 > > tablepivot: > > t1_id t2_id > ================= > 1 1 > 1 3 > 2 1 > 2 2 > > > what i need output is distinct records from table1 with the associated > titles from table2 grouped in the result. > > the relevant bit of my sql statement as it stands now is: > > select distinct t1.title, t1.desc, t2.title from table1 as t1, tablepivot as > tp, table2 as t2 where t1.id = tp.t1_id and t2.id = tp.t2_id and > (t1.title+t1.desc like '%whatever%' or t2.titles like > '%whatever%') > > but without the t2.title somehow being grouped up, this results in records > from table1 being repeated in the recordset when associated with multiple > titles from table2. > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238948 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
