Re: Working with multiple selects? - Mailing list pgsql-novice

On Wed, 2002-04-17 at 04:30, Juliet May wrote:
> 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:

SELECT
> 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
FROM contact, contracts, landowner, fields,
     soil_type, field_prep_method, ag_district
WHERE contact.contact_id = contracts.contact_id
  AND fields.contract_id = contracts.contract_id
  AND landowner.landowner_id = fields.landowner_id
  AND ag_district.ag_district_id = fields.ag_district_id
  AND soil_type.soil_type_id = fields.soil_type_id

And then you blew it by not defining the field_prep_method table..
  AND field_prep_method.field_prep_method_id = ???

You presumably want to link to that through the "activity" table, but it
becomes unclear...

You will probably also want to do an 'EXPLAIN ...' before you do that
query, you will definitely want to ensure your statistics are up to date
with an 'ANALYZE' ('VACUUM ANALYZE' if you are running 7.1 or earlier).

You could also do (some of) those things as:

FROM
  contact INNER JOIN contracts USING ( contact_id )
          INNER JOIN fields USING ( contract_id )
          INNER JOIN landowner USING ( landowner_id )
          INNER JOIN ag_district USING ( ag_district_id )
          INNER JOIN soil_type USING ( soil_type_id )

And if you have defined PRIMARY KEY and FOREIGN KEY in your table
definitions you can probably specify NATURAL JOIN ... AND leave off the
"USING ( ... )"

Hope this is some help,
                    Andrew.

>
> 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)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?