Working with multiple selects?

From: "Juliet May" <jmay(at)speark(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Working with multiple selects?
Date: 2002-04-16 16:30:42
Message-ID: 006401c1e564$0da14e50$a1d2a8c0@sweetbeet2k
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have a similar issue to the post about adding two select statements
together and I'm not sure which direction to go for my solution. I'm very
novice at both pgsql and sql. 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? 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 but I have to get this
done.

I need the following information about the individual:
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)

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Patrick Coulombe 2002-04-16 16:34:10 pgaccess
Previous Message Josh Berkus 2002-04-16 15:31:41 Re: Adding two select statements together