Query Counting returns in an inner join.

[NewbieDBA]

Hi
I have an ongoing question. I am trying to get this query to work and I am having the devil of a time working it out.
So:

SELECT PHONE.PHONE, PHONE.PHONE_TYPE,
PHONE.CUSTOMER,
MEETING_REGISTRANT.SHIP_CUSTOMER,
MEETING_REGISTRANT.MEETING
FROM PHONE INNER JOIN
MEETING_REGISTRANT ON
PHONE.CUSTOMER = MEETING_REGISTRANT.SHIP_CUSTOMER
WHERE (PHONE.PHONE_TYPE = 'EMAIL')

I would like to be able to put a filter on this that will return the MEETING_REGISTRANT.SHIP_CUSTOMER WHERE (not sure what to do now) [where that customer has three or more meeting orders]. Only way I see how is I get a return on a SHIP_CUSTOMER who repeats three times in this Column. How do I phrase this keeping in mind my SHIP_CUSTOMER s are numbers.

I am really new to this without much training.
Thanks
Adam

 

[tlbroadbent]

Try this query which uses a correlated subquery to count the meetings.

SELECT P.PHONE, P.PHONE_TYPE,
P.CUSTOMER,
M.SHIP_CUSTOMER,
M.MEETING
FROM PHONE P INNER JOIN
MEETING_REGISTRANT M ON
P.CUSTOMER = M.SHIP_CUSTOMER
WHERE (P.PHONE_TYPE = 'EMAIL')
AND EXISTS (SELECT SHIP_CUSTOMER, COUNT(MEETING)
FROM MEETING_REGISTRANT
WHERE SHIP_CUSTOMER=P.CUSTOMER
GROUP BY SHIP_CUSTOMER
HAVING COUNT(MEETING)>=3)
Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[NewbieDBA]

Terry. You're a master.
Thanks