Обсуждение: Adding two select statements together
I don't know if the subject line is a correct description I have three tables for entering time data for projects. The three tables are timeentries, projects, and projectstatus. timeentries references the project id from projects and projects references the projectstatus table I want to select all of the projects that aren't defined as "Inactive" like so SELECT testproject.*, teststatus.name FROM testproject INNER JOIN teststatus ON testproject.status = teststatus.id WHERE (teststatus.name<>"Inactive"); but I also want to include any projects that are referenced by the timeentries between two dates (without having duplicate project listings) This added complication is where I get totally lost - any help?
Brian,
> I have three tables for entering time data for projects.
>
> The three tables are timeentries, projects, and projectstatus.
> timeentries
> references the project id from projects and projects references the
> projectstatus table
This sounds nightmarish ... do you have any design control over the
database?
> I want to select all of the projects that aren't defined as
> "Inactive" like
> so
> SELECT testproject.*, teststatus.name
> FROM testproject INNER JOIN teststatus ON testproject.status =
> teststatus.id
> WHERE (teststatus.name<>"Inactive");
>
>
> but I also want to include any projects that are referenced by the
> timeentries between two dates (without having duplicate project
> listings)
What's wrong with changing the where clause to:
WHERE (teststatus.name<>"Inactive")
OR EXISTS ( SELECT timeentries.projectid
FROM timeentries
WHERE timeentries.dateentered BETWEEN $start_date AND $end_date
AND projectid = testproject.projectid);
You should pick up a SQL book to learn about more clauses like EXISTS.
See:
http://techdocs.postgresql.org/techdocs/bookreviews.php
For listings of a few books.
-Josh Berkus
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)
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?