Deanna,
It looks like you are trying to perform a left outer join between a single
table on the left and two inner joined tables on the right. If that's the
case, I've tried to do this several times - also in Access - and never
gotten it to work. There is a workaround, though. Whether you consider it
advisable is up to you.
You can create a query _in_Access_ that performs the inner join and then
reference the Access query as a table. Works pretty slick and doesn't seem
to negatively affect performance. The downside is that you are stuck with
the Access query which you gotta remember to have in your database (if you
have multiple copies...dev & prod, etc.) and which you may have to change
to add/remove fields in the future.
Hope this helps, I'll be watching the thread for other, wiser ideas.
Kevin
At 11:26 AM 4/17/00 -0500, you wrote:
>This is slightly off topic, but I'm sure there are sql gurus out there who
>know how to do this.
>
>I'm trying to join three tables (category, subcat, catrelation), pulling all
>records from category, and using the <cf_twoselectsrelated> tag to output
>the results, so that if someone chooses a category, the second select box is
>populated with the appropriate subcategories. I have it working when I don't
>care if the categories that don't have subs are included. But I do care! So,
>how do I get this to work in one query that I can reference in the custom
>tag?
>
>Oh yah, and this is in access right now. The error I get is "join type not
>supported." I've already tried going back to access to create the query, and
>it basically tells me I have to do it in two queries....
>
>Grrr. Frustrated.
>
><cfquery name="pullcats" datasource="#application.dsn#">
>SELECT c.cat_id, c.cat_name, s.subcat_id, s.subcat_name
>FROM category c left outer join catrelation r on c.cat_id = r.cat_id, subcat
>s
>WHERE c.cat_id = r.cat_id AND
> s.subcat_id = r.subcat_id
></cfquery>
>
>THanks!
>-d
>
>************************************************************
>Deanna Schneider
>Interactive Media Developer
>UWEX Cooperative Extension Electronic Publishing Group
>302 Hiram Smith
>1545 Observatory Dr.
>Madison, WI 53706
>(608) 265-7923
--------------------------
Kevin Marshall
Web Application Developer
eCalton.com, Inc.
Vero Beach, FL
www.ecalton.com
[EMAIL PROTECTED]
561.569.4500
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
