multiple queries into one query

[sweeny500]

Hi All

 

I think I have a performance problem with my queries.

I have two tables.

 

Agents and Properties

 

In the result I need to get each agents full details (in a certain county) and then the full details of each  property for every

 

agent returned.

 

At the moment I query the agents table and while looping through these I query the properties table for each agent. I think

 

there must be a quicker way using joins or subqueries but I can't work it out. Any Help much appreciated

 

 

Table Agents....

 

Agent ID, Agent Name, Agent Address, AgentCounty

 

 

Table Properties.

 

Agent ID, PropertyAddress, Bedrooms, Bathrooms, PropertyType

 

 

 

 

 

PS. I will be using the results to populate an xml file something like this...

 

 

 
<properties>
     <entry>
         <agents name="John Smith"  address="20 Bridge Street" county="Tilby"/>
           <prop>
                <address>25 Sea View</address>
                <bedrooms>5</bedrooms>
                <bathrooms>3</bathrooms>
                <propertytype>Duplex</propertytype>
           </prop>
     </entry>     
</properties>

[toplay]

Based on what I think you want, try something like this:

 

select

        *

from  Agents

join    Properties

using  (Agent_ID)

 

where AgentCounty = 'something'