From: Robin Bowes
Date: April 30 1999 12:05pm
Subject: SQL query problem
List-Archive: http://lists.mysql.com/mysql/2687
Message-Id: <37299C97.9197C89@eoc.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.