Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Posts
    107

    Unanswered: select * from table, but do a look up first..?

    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?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Feb 2003
    Posts
    107

    datatypes

    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?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by dotolee
    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.?

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by dotolee
    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?
    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...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Feb 2003
    Posts
    107

    data typing

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •