cfinclude query: return result for specific row

quote:

---

Yes it makes sense, but my suggestion was that one associates the book
to the most specific subject.

---

That's a good point - sorry I misinterpreted your original statement. That would be the least redundant way to associate a book to multiple hierachical subjects. However, there would be no way to easily retrieve a book and all its subjects as very few DBMS support recursion. You could of course write three subqueries (if you are assuming you only ever have three levels of subject) to retrieve those subjects, but that removes the scalability from the approach, as more subjects would mean more subqueries.

The many-to-many relationship also allows the possibility of multiple top-level subjects to be associated to a book (although that is currently beyond the scope of the requirements as I understand them).

Basically I'm saying either approach would work, although my feeling is the many-to-many approach is easier to read in a scalable fashion without writing some sort of stored procedure.

Marianne,

Glad I could help. To meet your requirement it seems like you would add one field to my schema in the Subjects table:

parentSubjectId number (FK)

That would simply be a recursive reference to another subject's ID to which it falls under. Maybe that doesn't make sense so here's some example data:

subjectId, subjectName, parentSubjectId
1, "Language Arts", null
2, "Reading", 1
3, "Phonics", 2
4, "Other Subject with no parent", null

So you can see we have built ourselves a nice hierarchy, where subjects which fall under other subjects can reference them. When you go to allow subjects to be chosen you can then query after each drop-down selection by refreshing the page (or via AJAX) to get its subjects. In this case we could also possibly do away with the "Ranking" field in our linking table since our hierarchy is captured in the relationships we now have, but for the sake of making our lives easy, let's keep it.

Like let's say we select "Language arts" with a subjectId of 1, we can get its child subjects like:

<cfquery name="getChildSubjects" datasource="#Request.dsn#">
SELECT s.subjectId, s.subjectName
FROM subjects s
WHERE s.parentSubjectId = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.subjectId#" />
ORDER BY s.subjectName
</cfquery>

Obviously, modifications would need to be made to fit your schema, variable names, etc. but that's the general principles.

As for how to associate textbooks to subjects you will actually be inserting one record into the linking table for each subject selected. So let's say you have a book "XSS for dummies."

To insert the book you would add a single record in your textbook table, maybe something like:

INSERT INTO textbook (
textbookId,
textbookName,
<!--- Other fields go here, etc... --->
) VALUES (
1,
'XSS for dummies',
<!--- Other fields go here, etc... --->
)

Now, the user has selected three subjects: Computer Science (ID: 5), Internet and Web Topics (ID: 11), and Security (ID:98). We will thus insert three records into our linking table like:

INSERT INTO textbookSubjects (
textbookId,
subjectId,
ranking
) VALUES (
1,
5,
'Primary'
)

INSERT INTO textbookSubjects (
textbookId,
subjectId,
ranking
) VALUES (
1,
11,
'Secondary'
)

INSERT INTO textbookSubjects (
textbookId,
subjectId,
ranking
) VALUES (
1,
98,
'Tertiary'
)

So now when we query to get all our textbooks and related subjects, we have something like this:

SELECT t.textbookId, t.textbookName, s.subjectId, s.subjectName, ts.ranking
FROM textbook t
LEFT JOIN textbookSubjects ts
ON t.textbookId = ts.textbookId
LEFT JOIN subject s
ON t.subjectId = s.subjectId
ORDER BY t.textbookName

An example dataset might look like:

textbookId, textbookName, subjectId, subjectName, ranking
1, "XSS for Dummies", 5, "Computer Science", "Primary"
1, "XSS for Dummies", 11, "Internet and Web Topics", "Secondary"
1, "XSS for Dummies", 98, "Security", "Tertiary"
2, "Some Other Book With No Subjects", null, null, null

Now if we iterate over this dataset, you will end up outputting the textbook 3 times, which is probably not what you wanted. So to control this we can do something like:

<cfoutput query="myBooks" group="textbookId">
<tr>
<td>#myBooks.textbookName#</td>
<!--- Show all the subjects --->
<td>
<!--- These cfoutput tags with no group will give you all the values for the current group --->
<cfoutput>
#myBooks.subjectName#<br />
</cfoutput>
</td>
</tr>
</cfoutput>

These examples are obviously brief but I hope they get the point across and help you accomplish what you're trying to do.

Thanks Ken - I'll post the table structures tomorrow when I get to work.
I'm still pretty new to this stuff so I might need some hand holding