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.
