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)
