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:238946 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
