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:238949
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
