This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C1E6A7.903E8D70
Content-Type: text/plain;
	charset="iso-8859-1"

You should be able to pull that out using one SELECT query with a few JOINs.
Things only need to move on from basic joins to subqueries when you have a
very complex query.

The situation you mentioned with the landowners might complicate things a
little. To create a many to many join (ie one contract for multiple
landowners, and multiple contracts for on landowner), you need to add
another table:

landowner_contract
landowner_id	FK
contract_id  FK

Anyway for this I will assume that you don't want this, just to make the SQL
easier. If you did move to the other model it only adds one more join.

I am not entirely sure of the syntax for postgresql, but this is very likely
to work.

SELECT contacts.first_name, contacts.last_name, contacts.email,
	landowner.name, fields.contracted_acres,
	fields.abandoned_acres, soil_type.soil_type_description,
	field_prep.field_prep_method, ag_district.ag_district_name
FROM contacts
	JOIN contract_contacts ON
	contract_contacts.contact_id = contacts.contact_id
	JOIN landowner ON
	landowner.contract_id = contract.contract_id
	JOIN fields ON
	fields.landowner_id = landowner.landowner_id
	JOIN soil_type ON
	soil_type.soil_type_id = fields.soil_type_id
	JOIN activity ON
	activity.field_id = field.field_id
	JOIN field_prep ON
	field_prep.activity_id = activity.activity_id
	JOIN ag_districts ON
	ag_districts.ag_district_id = fields.ag_district_id
WHERE contacts.contact_id = <insert ID here>

Are you sure you want to return all this at once? Depending on how you have
set up your tables you might end up with a lot of rows being returned, eg if
there are multiple fields for one contact, you end up with at least the
number of rows that there are in the fields table.

The other problem you may run into is that you may want OUTER joins. eg if
you have a contact who does not have any fields, then with the current query
you wouldn't get anything returned. Outer joins are slower, but return
records reguardless of whether they have a corresponding record in the table
they are joined to. You have left, right and full outer joins, but
postgresql may work this out for you.

Hope this works for you, feel free to email if it doesn't.

regards
David Cameron
nOw.b2b
dcameron@i...

>  -----Original Message-----
> From: 	Juliet May [mailto:jmay@s...] 
> Sent:	Wednesday, 17 April 2002 2:35 AM
> To:	professional php
> Subject:	[pro_php] working with multiple selects
> 
> I need to pull a multiple fields from multiple
> tables to describe a contract for the individual that logs onto my
website.
> I have one field that is the unique identifier for the person that logs on
> to the website  (contacts.contact.id).
> 
> Basically my question is should I use views? subqueries? variables to hold
> the results of different select statements? joins? The database I am using
is postgresql. I'm really not sure where to even begin to pull out the
information that I need. I really appreciate any help you can provide. I am
in way over my head (once again) but I have to get this done.
> 
> I need to present the following information about the individual in a web
page (next step is to give them a pre-filled out form where they can modify
their data):
> contact.first_name
> contact.last_name
> contact.email
> landowner.name
> fields.contracted_acres
> fields.abandoned_acres
> soil_type.soil_type_description
> field_prep_method.field_prep_method_description
> ag_district.ag_district_name
> 
> I am using the following tables and fields (I did not include the fields
> that I do not need to extract data from). Any field that is called _id is
> either a primary key or a foreign key. If it is a primary key it has the
> same name as the table. Any suggestions on reconfiguring my database would
> also be appreciated. I have a total of about 45 tables. The rest of the
> tables refer to different activities.
> 
> CONTACTS
> contact_id
> first_name
> last_name
> email
> 
> CONTRACT_CONTACTS
> contract_id (fk)
> contact_id (fk)
> (links the contacts with all of the contracts they are associated with)
> 
> FIELDS
> field_id
> contract_id
> contracted_acres
> abandoned_acres
> landowner_id
> soil_type_id
> ag_district_id
> 
> LANDOWNER
> landowner.id
> landowner.name
> (each field is associated with a different contract at this point, this
> might change with multiple fields associated with one contract)
> 
> SOIL_TYPE
> soil_type_id
> soil_type_description
> 
> AG_DISTRICTS
> ag_district_id
> ag_district_name
> 
> ACTIVITY
> field_id
> occurance_id
> activity_type_id
> 
> FIELD_PREP
> occurance_id
> field_prep_method
> 
> ACTIVITY_TYPES
> activity_type_id
> activity_type_description (looking for field preparation activity)
> 
> 
> ---