Oops... should be:
SELECT Realtor.Name, PropType.Name
FROM Realtor, PropType
WHERE Realtor.RealtorID = RealtorAndPropType.RealtorID
AND RealtorAndPropType.PropTypeID = PropType.PropTypeID
>Before you go any further, it would be wise to normalize your db design....
>
>each field in the db should be atomic - it should contain a single
>value (not a list of values)...
>
>Assuming your table is a realtor table, you need another table to
>associate the list of realtors with the list of property types
>available from that realtor.
>
>So, you have something like this:
>
>
> Realtor
>
> RealtorID Integer Identity Primary Key,
> Name VarChar(50)
> .
> .
> .
>
> PropType
>
> PropTypeID Integer Identity Primary Key,
> Name VarChar(50)
>
> RealtorAndPropType
>
> RealtorID Integer References Realtor,
> PropTypeID Integer References PropType
> Primary Key (RealtorID, PropTypeID)
>
>
>Once you have proper db design, then SQL will reward you by doing
>all the work (it is designed to do that)...
>
>So you will have a query something like:
>
> SELECT Realtor.Name, PropType.Name
> FROM Realtor, PropType
>
> WHERE Realtor.RealtorID = RealtorAndPropType.RealtorID
> AND RealtorAndPropType.PropTypeID = RealtorAndPropType,PropTypeID
>
>
>That gives you a query which contains all the property types for
>each realtor..
>
>SQL handles this very efficiently & the above will give the added
>benefit of referential integrity in your db.
>
>HTH
>
>Dick
>
>
>At 12:39 PM -0700 6/9/2000, Cyrill Vatomsky wrote:
>>Hi, All,
>>
>>One of the fields (property.type)in my query contains a comma-delimited list
>>of numbers (1,3,4)
>>I have a lookup table (types) that consists of two fields: ID and Name.
>>
>>Now, I need to replace numbers in Property.type with names from Type.Name.
>>How could that be done with ColdFusion?
>>
>>
>>-----------
>>
>>The reason I am doing it this way is because I get an empty recordset if I
>>try to do it in SQL:
>>
>>SELECT Property.ID, Property.Type, Type.ID, Type.Name
>>FROM properties
>>WHERE str(type.id) in ('property.type')
>>
>>
>>I would definately prefer the SQL solution...
>>
>>Cyrill
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
