List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 11 1999 6:28pm
Subject:Re: SQL query problem
View as plain text  
Robin Bowes wrote:
> 
> Hi,
> 
> I have a query requirement which seems to be cropping up regularly but
> to which I've never actually found a satisfactory solution.  I'll
> outline the problem using minimal tables (ie I won't cloud the issue
> with tables/fields that are not relevant).
> 
> Consider the following relations:
> 
> Enquiry    Contact  Role         EnquiryContactRole
> =======    =======  ====         ==================
> Ref (*)    Ref (*)  Ref (*)      Enquiry_Ref (*)
> DateTaken  Name     Description  Contact_Ref (*)
>            Address               Role_Ref
> 
> (*) denotes the primary key for each table.
> 
> Role is a static table and contains the following data:
> 
> +-----+-----------------+
> | Ref | Description     |
> +-----+-----------------+
> |   0 | Not specified   |
> |   1 | Enquirer        |
> |   2 | Complainant     |
> |   3 | Respondent      |
> |   4 | Other Party     |
> |   5 | OBO Complainant |
> |   6 | Applicant       |
> |   7 | Representative  |
> +-----+-----------------+
> 
> Contacts are linked to Enquiries by entries in the EnquiryContactRole
> table.
> Each Contact may appear a maximum of once in each enquiry, ie they can
> only have a single role.
> 
> A query to link the tables might look something like this:
> 
> select
>    e.Ref,
>    e.DateTaken,
>    c.Name,
>    c.Address,
>    r.Description as Role
> from
>    Enquiry e
>    join Contact c
>    join EnquiryContactRole ecr
>    join Role r
> where
>    ecr.Enquiry_Ref = e.Ref
>    and c.Ref = ecr.Contact_Ref
>    and r.Ref = ecr.Role_Ref
> order by e.Ref
> 
> Sample output would look like this:
> 
> ( I have omitted the Contact details for confidentiality reasons )
> 
> +---------+------------+----------------+
> | Ref     | DateTaken  | Role           |
> +---------+------------+----------------+
> | 1061566 | 1999-12-31 | Respondent     |
> | 1061566 | 1999-12-31 | Applicant      |
> | 1068311 | 1999-11-26 | Enquirer       |
> | 1101651 | 1999-04-22 | Respondent     |
> | 1101651 | 1999-04-22 | Applicant      |
> | 1101654 | 1999-04-07 | Respondent     |
> | 1101654 | 1999-04-07 | Applicant      |
> | 1101655 | 1999-04-07 | Applicant      |
> | 1101656 | 1999-04-07 | Respondent     |
> | 1101656 | 1999-04-07 | Applicant      |
> | 1101657 | 1999-04-07 | Enquirer       |
> | 1101658 | 1999-04-07 | Not specified  |
> | 1101658 | 1999-04-07 | Enquirer       |
> | 1101666 | 1999-04-07 | Applicant      |
> | 1101668 | 1999-04-06 | Respondent     |
> | 1101668 | 1999-04-06 | Applicant      |
> | 1101669 | 1999-04-06 | Respondent     |
> | 1101669 | 1999-04-06 | Applicant      |
> | 1101669 | 1999-04-06 | Representative |
> | 1101670 | 1999-04-06 | Respondent     |
> +---------+------------+----------------+
> 
> Now, here's my problem: I'd like to restrict this answer set to those
> Enquiries that have a Contact in the role of "Applicant" and DO NOT have
> a contact in the Role of "Representative".
> 
> Using the sample data above, I would therefore be interested in
> Enquiries 1061566, 1101651, 1101654, 1101655, 1101656, 1101666, and
> 1101668.  Note that Enquiry 1101669 has an Applicant but also has a
> Representative so I am not interested in this Enquiry.
> 
> It is relatively straight-forward to retrieve only those Enquiries with
> Applicants but can anyone suggest how I might discard those Enquiries
> which also have a representative?
> 
> Thanks,
> 
> R.

Hi Robin

Hope this answer don't get you too late :)

You have to use tables more than one time:
SELECT
	e.Ref,
	e.DateTaken,
	c.Name,
	c.Address,
	r.Description as Role
FROM
	Enquiry e
	join Contact c
	join EnquiryContactRole ecr
	join Role r
	join EnquiryContactRole ecr2
	join Role r2
WHERE
	e.Ref = ecr.Enquiry_Ref
	AND c.Ref = ecr.Contact_Ref
	AND r.Ref = ecr.Role_Ref
	AND r.Description = 'Applicant'
	AND e.Ref = ecr2.Enquiry_Ref
	AND ecr2.Role_Ref = r2.Ref
	AND r2.Description != 'Representative'
ORDER BY e.Ref

Tschau
Christian

Thread
SQL query problemRobin Bowes30 Apr
  • Re: SQL query problemChristian Mack11 May