Querying related tables

[c1natra]

Simple query question -

I have a real estate table with various fields (Beds, Baths, SqFt, etc). One
of the fields is BrokerID, which is foreign key linked to the primary key in
the Broker table which holds names and contact info for each broker. I
understand table relationships and what not. No problem there.

Where I am lost is in the SQL query...here's an example.

SELECT *
FROM RealEstateTable
WHERE RealEstateID = 1

Let's say this query returns a record with a BrokerID of 3. How do I go about
pulling the appropriate broker info from the Broker table?

I know this is easy, but I'm not getting it.

Thanks for everyone's time...Bill

[Dan Bracuk]

select just the fields you want, not *
from realestatetable r, brokertable b
where t.brokerid = b.brokerid
and maybe other constraints

Originally posted by: c1natra
Simple query question -

I have a real estate table with various fields (Beds, Baths, SqFt, etc). One
of the fields is BrokerID, which is foreign key linked to the primary key in
the Broker table which holds names and contact info for each broker. I
understand table relationships and what not. No problem there.

Where I am lost is in the SQL query...here's an example.

SELECT *
FROM RealEstateTable
WHERE RealEstateID = 1

Let's say this query returns a record with a BrokerID of 3. How do I go about
pulling the appropriate broker info from the Broker table?

I know this is easy, but I'm not getting it.

Thanks for everyone's time...Bill

[c1natra]

Thanks Dan -

I'm assuming the "t" should be an "r"?

select just the fields you want, not *
from realestatetable r, brokertable b
where r.brokerid = b.brokerid



So then I simply call the broker information like this?

<cfoutput query="queryname">#b.BrokerInfo#</cfoutput>

Thanks for the quick reply...

Bill

[paross1]

No, you don't include the table prefix in the field output. If the field name
in the table is BrokerInfo, that is what you put, even though you may have
added the table prefix in the select statement.

<cfoutput query="queryname">#BrokerInfo#</cfoutput>

Phil

[c1natra]

Thanks guys, you've been a great help!

Bill