the simplest of all queries

[willyc]

Hi,

I use to be a bit of a wiz at coldfusion 4, but I havent used it in quite a WHILE! Now its at version 7 and what I remember doesnt seem to work anymore...

I am trying to write a 'news' page, it takes data from two tables... news and users.

It will display the title and story from the news table and the username from the users table. The two tables will be joined by the userid which is the same in both tables....

Here is my query, that use to work in old version 4!

<cfquery datasource=#db# name=test>
SELECT *
FROM news, users
WHERE news.userid = users.userid
</cfquery>

But it just comes up with this error:

Error Executing Database Query.
Type mismatch in expression.

The error occurred in C:\CFusionMX7\wwwroot\cfdocs\jericho\index.cfm: line 1

1 : <cfquery datasource=#db# name=test>
2 : SELECT *
3 : FROM news, users

Any help would be greatly appreciated, and once I have got over this rather easy milestone, I will be able to get on with it!

Thanks,
William

[Memento]

I have not used CF much since version 5... I would try running your query against the database outside of ColdFusion and fetching all the rows. That may help isolate where the problem is. If the problem turns out to be with the query, you may want to check the data type of the columns you are joining. Some database will automatically convert a string to a number which works fine until you bump into a string that cannot be converted because it has alpha characters in it. If they have different data types, try adding a TO_CHAR or TO_NUMBER conversion.

[k-sea]

You still need to set star per table even though you want all from each.

<cfquery datasource=#db# name=test>
SELECT news.*, users.*
FROM news, users
WHERE news.userid = users.userid
</cfquery>

[Memento]

You still need to set star per table even though you want all from each.

<cfquery datasource=#db# name=test>
SELECT news.*, users.*
FROM news, users
WHERE news.userid = users.userid
</cfquery>

Perhaps in some databases, but I know it is not the case with Oracle at least. However, your point makes me wonder what ColdFusion does when it gets back two columns named "userid". I could see how the confusion from that might cause an error.