Thanks Ben. We had a staff meeting yesterday, and my co-worker is out today. We'll get back to this Monday. Thanks for your help. I know this last email opened her eyes a little. :)
Ray Ben Nadel wrote: > Ray, you could get something like this: > > ID Title COUNT > ---- -------------- --------- > 1. Title One 0 > 2. Title Two 9 > 3. Title Three 2 > > > OR something like this inner join: > > ID Title Titles > ---- -------------- --------- > 2. Title Two Foo > 2. Title Two Bar > 2. Title Two Blam > 2. Title Two Content > 2. Title Two Me > 3. Title Three Hello World > 3. Title Three Best Seller > > > Then, if you want, you can do a Query of Queries to get the ID/Title from > the second query: > > SELECT DISTINCT > id, > title > FROM > qTitles > > Which would give you distinct id/title for all titles that have joined > titles.... > > But, what you CANNOT do (to my best knowledge) is have something like: > > ID Title Titles > ---- -------------- --------- > 1. Title One Title 1, title 2, title 3, title 4 > 2. Title Two foo, bar , blam, test, whats up > 3. Title Three several, different , joined, titles, matched here > > ....................... > Ben Nadel > www.bennadel.com > -----Original Message----- > From: Ray Champagne [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 27, 2006 2:59 PM > To: CF-Talk > Subject: Re: SQL join help > > 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:239040 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
