I have a table that references another table for full descriptions.
I need to select * from table 1, but for certain fields, instead of just returning a number, I need it to look up the descriptions and return them in the record details.

eg)
I want to select all fields from File Table but have it look up info from a table called SubFileType

The sql I tried so far is:

SELECT FILEID, FILENUM, f.DESCRIPTION, NAME, s.SUBFILETYPE, STATUS FROM FILE F, SUBFILETYPE S WHERE s.SUBFILETYPEID=f.SUBFILETYPE

The error is : Type Mismatch in expression.
Assuming the SQL syntax is correct, could this be because the SubFileTypeID in the Subfiletype table is an Autonumber, but in the file table, I'm treating it as text?

Yes it could. Foreign keys and their corresponding primary/unique keys should always be of the same datatype. You may be able to get around this using CAST(column AS NUMBER) or similar. But preferably, change the table so that data types are consistent.

Thanks.
It worked.... but I was always under the impression that it's best to keep all fields in the db (except pks) as text... and then let the application decide how to use the information.
??
any comments on that theory?

Yes - but they are too rude to print

You have been misinformed: it is always best to use the most appropriate data type for each column in the table, so that the application is forced to use it correctly.

I hope you are not similarly under the false impression that primary keys, unique keys, foreign keys and check constraints are all to be avoided and that the application should decide what is a duplicate, what is an invalid reference, etc. etc.?

If that were the case, then what would be the use of having any other data type?
Data type'ing is a form of integrity constraint. Number type must hold numbers, dates - dates, etc...

no. (regarding the PK, FK)
But thanks for the tid bit re: other fields. I was informed that generic types was the way to go when you have more than one data consumer... each app can decide how to use the data on their own...
But I guess that can still work if you enforce the proper type in the db ...
I'll put it to use!