Exclusion Query Logic Issues

[NewbieDBA]

Hi Everyone

My sense and logic have 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.
Advice? Third table? The sub-query I built seemed to fail an well. Any help is appreciated.
--------------------------------------------

SELECT DISTINCT FORUM01.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'))FORUM01


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 FORUM01.SHIP_CUSTOMER = ALLELSE.SHIP_CUSTOMER
ORDER BY FORUM01.SHIP_CUSTOMER


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


Thanks
Adam

 

[tlbroadbent]

Are you running this query in MS Access, SQL Server or another RDMS? Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[mdthornton]

Not sure I understand you data but I would write a query for all people who have attended 1072, q1072.

Write another query for all people who have attended meetings not in the FOR class, qNotFOR.

Now just inner join q1072 to qNotFor. Good Luck,
Mike T

 

[NewbieDBA]

Thank you both for replying:

Terry,
I am running this in MS Query Analyzer that came with SQL Server 7 service pack 5.

Mike,
I think I am misunderstanding you.

The query is set up essentially as you have suggested. The q1072 = FORUM01 which is inner join[ed] to qNotFOR = ALLELSE.

The issue I have is that, where a ship_customer meeting is = to 1072 individuals are drawn in who have other FOR class meetings [Forum01]. 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?

 

[tlbroadbent]

I asked the question about how you run the query because this is an Access forum and the syntax you posted looks like SQL Server syntax. You'll get a better and hopefully quicker resply in the SQL Server forum (forun183). Access Jet SQL sysntax often differs from SQL Server, significantly. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[mdthornton]

in your example, customer A would not be in the qNotFor recordset.... Good Luck,
Mike T