Chris,
Usually, you'd want to create a third table to contain the relationships
associating the listings table with the agents table (a many to many
relationship). This table might only need two columns - listingid and agentid.
Then just add a record for each agent that shows a property. You can add
additonal columns as needed which are unique to the relationship, for example
the date shown.
With your current table structure, you could use something like the following,
using the SQL 'IN' operator. The first query pulls the listing, the second
pulls all the agents' info. This assumes you're placing the agentid's in a
comma-delimited list within the 'agents' column of the listings table.
<cfquery name="listing" datasource="myDSN">
SELECT *
FROM listings
WHER listingid = '#url.propertyid#'
</cfquery>
<cfquery name="agents" datasource="myDSN">
SELECT *
FROM agents
WHERE agentid IN (#listing.agents#)
</cfquery>
Jim
-----Original Message-----
From: Chris Giminez <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Sunday, April 30, 2000 7:57 PM
Subject: Re: tying two tables
>thanks for the reply, but that's not what I am trying to do. The only way I can
think to accomplish
>this is to use multiple - separate queries. If someone can show me how to
improve this, I'd
>appreciate it.
>
>Let me give you an example:
>
>Say I run a query for a real estate listing (this isn't what I'm doing, but it
correlates well)
>
><CFQUERY NAME="property_detail" DATASOURCE="properties">
>SELECT *
>FROM Listing
>WHERE (Listing.Id = '#url.id#')
></CFQUERY>
>
>Now, lets say this returns the address, city, zip, price, etc along with 5 ID's
of agents that have
>shown this particular property. The format would be the agent's initials.
>
>Now I want to display the agent's full name and email on the same page.
>
>The table containing this might be something like:
>
>Agent_Id Name LName Email
>CSG Chris Giminez [EMAIL PROTECTED]
>...and so on....
>
>I am accomplishing this by setting up additional queries for each "agent"
>
><CFQUERY NAME="agent_name_1" DATASOURCE="properties">
>SELECT Agent_Id, Name, LName, Email
>FROM Agents
>WHERE (Agent_ID= '#property_detail.Agent_Id_1#')
></CFQUERY>
>
>Then I run another query for each Agent_Id needed.
>
>I'm sure there's a better way, but I don't know what.
>
>
>Chris Giminez
>Owner - Cyber Scriber Web Site Design
>831-728-4469
>ICQ# 2628000
------------------------------------------------------------------------------
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.
