Exclusion query logic problem

[NewbieDBA]

Hi Everyone

My sense and logic has left me. I need some help with my query.

I have one central table, meeting_registrant. I am attempting to find all of the people who have attended meeting 1072 who have attended any other offering, except for meetings in the meeting_class which 1072 belongs too [‘FOR’].

This was the current most successful try. It is still wrong. I am still getting people who are registered for excluded meetings. They're brought back in when I have someone who has a registration for 1072 and one meeting outside of the restricted group and one meeting in the restricted group. Advice? Third table? The sub-query I built seemed to fail as well. Here is the code:
--------------------------------------------

SELECT DISTINCT FOR.SHIP_CUSTOMER

FROM

(SELECT DISTINCT MR.SHIP_CUSTOMER

FROM MEETING_REGISTRANT MR
INNER JOIN CUSTOMER C ON
C.CUSTOMER = MR.SHIP_CUSTOMER
INNER JOIN ADDRESS A ON
C.ADDRESS_ID = A.ADDRESS_ID

WHERE MR.MEETING = '1072'

AND C.CUSTOMER_STATUS = 'A'
AND C.CUSTOMER_CLASS = 'INDIV'
AND A.ADDRSTATUS_CODE = 'G'
AND A.PRIMARY_FLG = 'Y'

AND MR.ORDER_STATUS IN ('A', 'H', 'P', 'N')
AND MR.REGIS_RATE NOT IN ('FAC','STAFF')) FOR

INNER JOIN


(SELECT DISTINCT MR.SHIP_CUSTOMER
FROM MEETING_REGISTRANT MR
INNER JOIN MEETING M ON
M.MEETING = MR.MEETING
WHERE M.MEETING_CLASS <> 'FOR') ALLELSE

ON FOR.SHIP_CUSTOMER = ALLELSE.SHIP_CUSTOMER
ORDER BY FOR.SHIP_CUSTOMER


----------------------------------------

The issue I have is that, where a ship_customer meeting is = to 1072 individuals are drawn in who have other FOR class meetings [FOR]. When you match up these folks with folks from qNotFOR [or ALLELSE] they are again present as they have one meeting other than 1072 or another FOR meeting.

Example:

Customer A
Meeting
1072, 806 (FOR class), 1094 (other)
Customer B
1072, 806 (FOR class)
Customer C
1072, 1094 (other)

I want Customer C
I get them, but I also get customer A. How do I get ride of customer A?


Any help is appreciated.
Thanks
Adam

 

[rac2]

Assuming Mr. Ship Customer will attend meeting 1072 only once, These are the customers who attended.

SELECT MR.SHIP_CUSTOMER
  FROM MEETING_REGISTRANT MR
  WHERE MR.MEETING = '1072'

These are the customers who attended meetings in other classes.

SELECT MR.SHIP_CUSTOMER
  FROM MEETING_REGISTRANT MR
  JOIN MEETING M ON MR.MEETING = M.MEETING
WHERE M.MEETING_CLASS <> 'FOR'

So these must be the people who attended meeting 1072 and a meeting in another class.

SELECT MR.SHIP_CUSTOMER
  FROM MEETING_REGISTRANT MR
WHERE MR.SHIP_CUSTOMER IN
     (SELECT MR.SHIP_CUSTOMER
        FROM MEETING_REGISTRANT MR
        WHERE MR.MEETING = '1072')
  AND MR.SHIP_CUSTOMER IN
     (SELECT MR.SHIP_CUSTOMER
        FROM MEETING_REGISTRANT MR
        JOIN MEETING M ON MR.MEETING = M.MEETING
        WHERE M.MEETING_CLASS <> 'FOR')

 

[NewbieDBA]

Your code is tighter than mine but I am still getting individuals who are registered for 'FOR' class meetings.
I had a new thought. What if I break out each of the 20 or so FOR class meeting into their own virtual tables. I picture a snowball rolling downhill in slow-motion reverse, shedding customers as it rolls through restrictions.

 

[NewbieDBA]

Bye the way thank you for your help rac2.

 

[NewbieDBA]

Solution!!!!!!!!!!!!!

SELECT OneTime.SHIP_CUSTOMER
FROM

(Select For01.SHIP_CUSTOMER
FROM
(SELECT SHIP_CUSTOMER, ORDER_STATUS, MEETING
FROM MEETING_REGISTRANT
WHERE (MEETING = '1072') AND (ORDER_STATUS <> 'C'))For01

LEFT OUTER JOIN

(SELECT MR.SHIP_CUSTOMER
FROM MEETING_REGISTRANT MR INNER JOIN
MEETING M ON MR.MEETING = M.MEETING
WHERE (MR.ORDER_STATUS <> 'C') AND (M.MEETING_CLASS = 'FOR')
GROUP BY MR.SHIP_CUSTOMER
HAVING (COUNT(*) >= 2))Mult
ON For01.SHIP_CUSTOMER = Mult.SHIP_CUSTOMER
WHERE Mult.SHIP_CUSTOMER IS NULL)OneTime

INNER JOIN

(SELECT SHIP_CUSTOMER
FROM MEETING_REGISTRANT
WHERE (ORDER_STATUS <> 'C')
GROUP BY SHIP_CUSTOMER
HAVING (COUNT(*) >= 2))Two
ON OneTime.SHIP_CUSTOMER = Two.SHIP_CUSTOMER
ORDER BY OneTime.SHIP_CUSTOMER

I am sure it could be streamlined. Thanks to everyone.
Adam