Inspiring

Answered

cfinclude query: return result for specific row

Forum|Forum|18 years ago
November 9, 2007
17 replies
3074 views

I've been trying to crack this all day and I feel like if I just had a nudge in the right direction I might be successful.

I have a query that returns the records for some textbooks that are at a specific school. I've normalized the textbook database so that the primary, secondary and tertiary subject categories are represented by the primary key of that category in its own table. As the query loops through the records, I want it to return the (varchar) subject category that goes with that (numerical) key in the table.

Since Coldfusion won't allow nested cfoutput, I've tried using cfinclude to grab the specific name for the subject category that goes with the current textbook - but what I've got so far returns all the subjects for the school id in each row - they're the right subjects! but I don't want all of them, just the right one with the right book/record. Can anyone suggest what I need to do? I'm attaching the code for the main page and for one of the cfincludes (they're identical except for "primary", "secondary" and "tertiary").

I realize I must be leaving something out but I can't figure out what. Thanks much in advance.

This topic has been closed for replies.

Correct answer

Holy cow, I got it to work! Code attached... and thank you so much, everyone!!! *whew*.

N

Newsgroup_User

Inspiring

oh, i just noticed a typo in paross1's code (or rather in your original
code that he copied/pasted) which causes your error:

in the FROM clause the 3 ts. in the left joins section should be changed
to tsu. (since the sp ss and st tables are joined to tsu table, not to
ts table)

hth

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

A

Anonymous

Hi, Azadi! :)

Well - I'm very confused at this point. Hope you can make head or tails of this. I'm about to chuck the whole thing and take William at FAA's suggestion to just use one table and try to learn about inner joins some other time! but if you can figure it out it would be nice, if it works, to have that to refer to as an example of "how to do it"...

I'm not sure why Phil added "TextbookID" - it's kind of an arbitrary thing - I thought the books should have a key. But it's not connected to anything at this point - TextbookSurveyUser is more of a security table than anything and doesn't otherwise have any connection to the other tables than the location the user hails from (SchoolID), which I'm trying to use to identify which books should be displayed. So - I tried removing the reference to "TextbookID" from the query - but it's still not working...

A

Anonymous

Correct answer

Holy cow, I got it to work! Code attached... and thank you so much, everyone!!! *whew*.

N

Newsgroup_User

Inspiring

post your current query sql

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

A

Anonymous

nope - still getting:

[Macromedia][SQLServer JDBC Driver][SQLServer]The multi-part identifier "ts.PrimarySubjectID" could not be bound.

M

MarianneValentineAuthor

Inspiring

Well, after I dl'd SQL Server Express, set everything up, and tried to create a new datasource in Coldfusion Administrator, I realized that my testing server runs queries with the SQL datasource on my webhost - so Access is not the problem.

Any more suggestions how I might get this thing to work? I'm really out of my depth here and I need to try to finish the app by the 15th if possible...

T

The_ScareCrow

Inspiring

Create your query using access itself. Then copy the query to your cf page.

In access, select query, then new query.
You can then add the required tables and select the required columns.
Note that you can right click the table join(s) to select which one you require.

Ken

M

MarianneValentineAuthor

Inspiring

Okay - I just dl'd SQL Server Express - I'll follow up when everything's set up! Thanks!

A

Anonymous

It's actually much more of a pain in the ass than that, you have to progressively nest them. See the article here as I'm about to cut out:

http://office.microsoft.com/en-us/access/HA012314871033.aspx

You'll also need to move your inner joins above your left joins. I would again suggest moving to SQL Server Express if at all possible to avoid these annoyances.

M

MarianneValentineAuthor

Inspiring

Okay - I'm a little confused - is this what you mean?

FROM TextbookSurveyUser AS tsu, TextbookSurvey AS ts, School AS s, SubjectCategoryPrimary AS sp(LEFT JOIN SubjectCategoryPrimary ON ts.PrimarySubjectID = sp.PrimarySubjectID), SubjectCategorySecondary AS ss(LEFT JOIN SubjectCategorySecondary ON ts.SecondarySubjectID = ss.SecondarySubjectID), SubjectCategoryTertiary AS st(LEFT JOIN SubjectCategoryTertiary ON ts.TertiarySubjectID = st.TertiarySubjectID)

P

paross1

Participating Frequently

You have tables "joined" in the FROM and as INNER or LEFT joins as well.... you can't combine syntax like that:

You can't do this:
FROM TextbookSurveyUser AS tsu, TextbookSurvey AS ts, School AS s, SubjectCategoryPrimary AS sp, SubjectCategorySecondary AS ss, SubjectCategoryTertiary AS st

and this:
LEFT JOIN SubjectCategoryPrimary ON ts.PrimarySubjectID = sp.PrimarySubjectID
LEFT JOIN SubjectCategorySecondary ON ts.SecondarySubjectID = ss.SecondarySubjectID
LEFT JOIN SubjectCategoryTertiary ON ts.TertiarySubjectID = st.TertiarySubjectID
INNER JOIN TextbookSurvey ts ON ts.textbookID = tsu.textbookID
INNER JOIN School s ON ts.SchoolID = s.SchoolID

in the same SELECT statement.

See attached.

Phil

A

Anonymous

Thanks very much for catching that, Phil - I will try this and post back later.

A

Anonymous

Oh I forgot there's an oddity with Access and performing JOINS, you have to nest the JOINs in parentheses. Kind of like

FROM (table 1 (INNER JOIN table2 ON field1 = field2))

And so on. That's why I hate developing Access. You won't have that problem in SQL Server. I would recommend quickly downloading SQL Server Express (which is free) and running your queries against that if at all possible. Otherwise you will probably have to unncecessarily tweak a lot of the SQL to suit Access and its BS flavor.

M

MarianneValentineAuthor

Inspiring

It's true - Access can be very techy :\

Nope - same error...
I remember having trouble with "LIMIT" at one point... whereas the code worked fine when I returned all results, if I tried to LIMIT to one, it threw an error.
Maybe I need to configure something differently?

A

Anonymous

According to your first LEFT JOIN statement, primarySubjectId is in another table:

tsu.PrimarySubjectId

Change the "ts" in the ORDER BY to "tsu" provided that is the correct table. And double check all the aliases to make sure the columns are referencing the correct tables.

M

MarianneValentineAuthor

Inspiring

Well - I had done that - here's my code:

A

Anonymous

I can't remember, but maybe Access doesn't like aliases in the ORDER BY. Change the ORDER BY to:

ORDER BY PrimarySubjectID, Grade1

See if that makes a difference.

- William

Show more replies

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded