Search results page

[absolut novice]

Hi - hopefully I can express my problem correctly - I'm just trying to create a
search results page from a query on a linked table. Current SQL:

SELECT MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME, MEMBERS.EMAIL,
MEMBERS.ACCESS
FROM MEMBERS
WHERE LAST_NAME = '#URL.LAST_NAME#'
ORDER BY LAST_NAME ASC

My problem is that "MEMBERS.ACCESS" contains the id values for a linked table.
How can I get these id values to display the actual "text" values taken from
the linked table. Many thanks for any help.

[rmorgan]

Without knowing your db structure, you will probably want to do a JOIN to that table.
[url]http://www.w3schools.com/sql/sql_join.asp[/url] is a pretty good site for the basics.

[absolut novice]

Thanks - for the web link - DB structure: MS Access/ Main Table "MEMBERS" Sub
Table "ACCESS" linked via "MEMBERS.ACCESS" and "ACCESS.ID". ACCESS.ACCESS
contains the text I want to diplay on the search returns page.

When I tried:

SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
MEMBERS.EMAIL, MEMBERS.ACCESS
FROM MEMBERS
RIGHT JOIN ACCESS
ON MEMBERS.ACCESS=ACCESS.ID
WHERE LAST_NAME = '#URL.LAST_NAME#'
ORDER BY LAST_NAME ASC

It works fine in the Test SQL Statement window however display the error "Too
few parameters. Expected 1" via the web brower.

SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
MEMBERS.EMAIL, MEMBERS.ACCESS
FROM MEMBERS
RIGHT JOIN ACCESS
ON MEMBERS.ACCESS=ACCESS.ID
WHERE LAST_NAME = '#URL.LAST_NAME#'
ORDER BY LAST_NAME ASC

[rmorgan]

Try an INNER JOIN.

[absolut novice]

Thanks but unfortunately still the same result - so far I've tried RIGHT JOIN,
LEFT JOIN and INNER JOIN with no luck.

The following:

SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
MEMBERS.EMAIL, MEMBERS.ACCESS
FROM MEMBERS
INNER JOIN ACCESS
ON MEMBERS.ACCESS=ACCESS.ID
WHERE LAST_NAME = '#URL.LAST_NAME#'

Again works fine in the Test SQL Statement window however display the error
"Too few parameters. Expected 1" via the web browser. As for datatypes:

MEMBERS.ACCESS = Data Type = Number/Field Size = Long Integer/Row Source =
SELECT [ACCESS].[ID], [ACCESS].[ACCESS] FROM ACCESS;
ACCESS.ID = Data Type = AutoNumber/Field Size = Long Integer
ACCESS.ACCESS = Data Type = Text/Field Size = 50

TIA

[absolut novice]

Hi - hopefully I can express my problem correctly - I'm just trying to create a
search results page from a query on a linked table.
My problem is that "MEMBERS.ACCESS" contains the id values for a linked table.
How can I get these id values to display the actual "text" values taken from
the linked table.

So far I've tried RIGHT JOIN, LEFT JOIN and INNER JOIN with no luck.

The following:

SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
MEMBERS.EMAIL, MEMBERS.ACCESS
FROM MEMBERS
INNER JOIN ACCESS
ON MEMBERS.ACCESS=ACCESS.ID
WHERE LAST_NAME = '#URL.LAST_NAME#'

Again works fine in the Test SQL Statement window however display the error
"Too few parameters. Expected 1" via the web browser. As for datatypes:

MEMBERS.ACCESS = Data Type = Number/Field Size = Long Integer/Row Source =
SELECT [ACCESS].[ID], [ACCESS].[ACCESS] FROM ACCESS;
ACCESS.ID = Data Type = AutoNumber/Field Size = Long Integer
ACCESS.ACCESS = Data Type = Text/Field Size = 50

TIA :o

[Dan Bracuk]

I can't see anything wrong with the query, except that it looks more
complicated than it needs to be. Just for fun, change this:

SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
MEMBERS.EMAIL, MEMBERS.ACCESS
FROM MEMBERS
INNER JOIN ACCESS
ON MEMBERS.ACCESS=ACCESS.ID
WHERE LAST_NAME = '#URL.LAST_NAME#'


to this
SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
MEMBERS.EMAIL, MEMBERS.ACCESS
FROM MEMBERS, ACCESS
wher MEMBERS.ACCESS=ACCESS.ID
and LAST_NAME = '#URL.LAST_NAME#'

[mxstu]

The INNER | RIGHT | LEFT JOIN is ANSI style syntax which is probably better than FROM table1, table2 style. However, I agree it does seems a bit bulky in comparison ;-)

[absolut novice]

Thanks heaps Dan for the amended code:

SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
MEMBERS.EMAIL, MEMBERS.ACCESS
FROM MEMBERS, ACCESS
where MEMBERS.ACCESS=ACCESS.ID
and LAST_NAME = '#URL.LAST_NAME#'

- again works fine in the TEST SQL statement window however via the web I get
the following error:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Too few parameters. Expected 1.

[mxstu]

absolut novice,

Use whichever style you prefer, but your original query is the recommended
ANSI style syntax.

... MEMBERS INNER JOIN ACCESS ON MEMBERS.ACCESS=ACCESS.ID ....

I don't see anything wrong with your query either. You might want to turn on
debugging and output the actual sql statement sent to the database, then copy
and past it into Access and see if it still works.

[absolut novice]

Thanks mxstu - I'll try to get the system admin people to enable the debugging
however they know nothing about ColdFusion. Does this mean that the TEST SQL
statement window doesn't accurately test the SQL via the database?

[Dan Bracuk]

Let's look at this error message:
Too few parameters. Expected 1.

Troubleshooting step number 1:
do a dump of url and see what you get for url.last_name.

[absolut novice]

Sorry Dan - I'm not sure what you mean by "do a dump of url"

[mxstu]

Try the easy debugging method first, like Dan is suggesting. You might also
want to try deconstructing the statement, one column at a time to see which
piece is giving you problems.

I don't usually use any of the IDE's database features, so I'm not much help
there. I wouldn't say that it doesn't accurately test the sql, but it use a
different method of communicating with the database than is used by cfquery.
Access is pretty lenient about what you're allowed to do within its IDE. CF
uses an odbc socket to communicate with the Access database through CFQUERY,
and the rules for ODBC are bit different and usually a bit more strict.

[Dan Bracuk]

To do a dump of url means to stick this in your template and run it:

<cfdump var="#url#">

[absolut novice]

many thanks for your explaination