Help with CF_Tag

[*thirtysixsquares*]

Is there a way to combine the following queries into one query so that I can
use a single query with CF_TwoSelectsRelated?
Or a way to use 2 queries with CF_TwoSelectsRelated?

<CFQUERY DATASOURCE="DB" NAME="cats">
SELECT *
FROM itemParentCats
ORDER BY itemParentCatID
</CFQUERY>

<CFQUERY DATASOURCE="DB" NAME="subcats">
SELECT *
FROM itemCats
WHERE itemParentCatID= '#cats.itemParentCatID#'
ORDER BY itemCatID
</CFQUERY>

Any insight would be greatly appreciated.

[*thirtysixsquares*]

Is there a way to combine the following queries into one query so that I can
use a single query with CF_TwoSelectsRelated?
Or a way to use 2 queries with CF_TwoSelectsRelated?

<CFQUERY DATASOURCE="DB" NAME="cats">
SELECT *
FROM itemParentCats
ORDER BY itemParentCatID
</CFQUERY>

<CFQUERY DATASOURCE="DB" NAME="subcats">
SELECT *
FROM itemCats
WHERE itemParentCatID= '#cats.itemParentCatID#'
ORDER BY itemCatID
</CFQUERY>

Any insight would be greatly appreciated.

[vkunirs]

Hi

i think its better to post your requirement than posting your queries. so that we can understand better.

[*thirtysixsquares*]

Requirements:
Use 2 queries with CF_TwoSelectsRelated

OR

Combine the 2 queries mentioned previously in order to offer 1 query to CF_Tag

[philh]

CF_2SR is made for this, that is, you need to join the two queries to get the
tag to work the way you want it to work. So, <CFQUERY DATASOURCE='DB'
NAME='cats'> SELECT * FROM itemParentCats ORDER BY itemParentCatID </CFQUERY>
<CFQUERY DATASOURCE='DB' NAME='subcats'> SELECT * FROM itemCats WHERE
itemParentCatID= '#cats.itemParentCatID#' ORDER BY itemCatID </CFQUERY>
becomes SELECT * FROM itemParentCats pcats JOIN ItemCats cats on
cats.itemParentCatID= pcats.itemParentCatID ORDER BY itemParentCatID I don't
think you need both key fields. It's bad form to have them both named the
same, since you have to designate one field as the key for the tag to work
properly. Specify the fields you want back. You should never, as a rule, use
SELECT *. HTH,

[philh]

Man, the edit function on this forum sucks.

[*thirtysixsquares*]

I don't understand the cat and pcat after the FROM.
Is there a way you could elaborate?

Thx

[paross1]

Because you are joining two tables by columns with the same name, you can
"alias" the table names to a shorter value and use that as the prefix on the
column name, or you can use the full table name in your join.

SELECT *
FROM itemParentCats pcats
JOIN ItemCats cats on cats.itemParentCatID= pcats.itemParentCatID
ORDER BY itemParentCatID

is the same as...

SELECT *
FROM itemParentCats
JOIN ItemCats on ItemCats.itemParentCatID= itemParentCats.itemParentCatID
ORDER BY itemParentCatID

Using table name aliases become mandatory when doing a self join (joining the
same table to itself).

Phil

[*thirtysixsquares*]

The Query throws an error at the FROM line in both methods.

[paross1]

1. What is the error?
2. What is your database?

Perhaps.....

<CFQUERY DATASOURCE="DB" NAME="cats">
SELECT *
FROM itemParentCats p, itemCats c
WHERE p.itemParentCatID = c.itemParentCatID
ORDER BY itemParentCatID
</CFQUERY>

Phil

[*thirtysixsquares*]

ERRORS:
Well at one point I was getting "Data type mismatch"
Now all I seem to get is "The specified field 'itemParentCatID' could refer to
more than one table listed in the FROM clause of your SQL statement. "

It would probably make life easier if the parent cats and the subcats were
just in one table, eh?
I suppose there's still time to do this, but is it necessary?

[*thirtysixsquares*]

I have pulled them into one table. Now each category record has one itemCatID
(one itemCatName) and one itemParentCatID. The itemParentCatID refers to the
itemCatID of another record in the same table. EX:
1 First Main Category 0
2 Subcategory 1
3 Second Main Category 0
2 Subcategory 3

MS Access DB

The first drop down should report the itemCatName (name=maincat
value=itemCatID) WHERE itemParentCatID = 0
Which should then populate the second dropdown with itemCatName (name=subcat
value=itemCatID) WHERE itemParentCatID = maincat.itemParentCatID

Does this make the query more clear, to do it all in one table?

Thank you so much for your help, Phil.

[paross1]

The reason that you are getting the error The specified field 'itemParentCatID'
is because you are doing a SELECT * from both tables, and each has a column
with the same name. You should select distinct column names instead of using
the * wildcard, adding the prefix of the table alias or full table name to each
column name selected in order to eliminate ambiguity.

As for your combined table, sorry, it makes no sense to me, but if you can get
what you need by combining both, without de-normalizing the data, then go for
it.

Phil

[*thirtysixsquares*]

Okay...the query's working
the two selects show the cats...and almost work

The parent cats display fine, but don't submit to the action page
The sub cats display the subs of the first parent cat but don't "refresh" to
another set of subcats if the parent cat select is changed

Any ideas??