SUB Query is no running

[NewbieDBA]

Hi
I wrote a little query to compare to lists in our database. One list is tracking organization belonging to a meta organization. The other list is tracking some individual customers. Because they are both considered customers [living in the customer table] they have separate customer numbers, ruling out a simple query. What I am attempting to do is pull up all the individuals in the one list called 'PurPer' and then using the CO_Customer field from their records pull the individuals who's company is in the 'Corp'. Wow. What is wrong with my sub-query. Query analyzer says the second WHERE clause is bad. What’s the deal? How hopeless am I?


SELECT CUS_DEMOGRAPHIC.CUSTOMER,
CUS_DEMOGRAPHIC.TYPE, CUS_DEMOGRAPHIC.CODE,
CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME,
CUSTOMER.NAME_TITLE, CUSTOMER.JOB_TITLE,
CUSTOMER.PHONE_TYPE, CUSTOMER.PHONE,
CUSTOMER.CUSTOMER_CLASS, CUSTOMER.CO_NAME,
CUSTOMER.CO_MAIL_NAME, ADDRESS.ADDRESS_1,
ADDRESS.ADDRESS_2, ADDRESS.CITY, ADDRESS.STATE,
ADDRESS.ZIP, ADDRESS.PRIMARY_FLG,
CUSTOMER.CO_CUSTOMER
FROM CUS_DEMOGRAPHIC INNER JOIN
CUSTOMER ON
CUS_DEMOGRAPHIC.CUSTOMER = CUSTOMER.CUSTOMER INNER
JOIN
ADDRESS ON
CUSTOMER.CUSTOMER = ADDRESS.CUSTOMER
WHERE (CUS_DEMOGRAPHIC.TYPE = 'PURPER') AND
(ADDRESS.PRIMARY_FLG = 'Y') AND
(CUS_DEMOGRAPHIC.CODE <> 'SV')
AND
EXISTS (SELECT CUS_DEMOGRAPHIC.CUSTOMER
FROM CUS_DEMOGRAPHIC INNER JOIN
CUSTOMER
WHERE CO_MAIL_NAME = CUS_DEMOGRAPHIC.CUSTOMER AND
CUS_DEMOGRAPHIC.CODE = 'Corp')

Thanks
Adam

 

[tlbroadbent]

The Inner Join cluase requires a Join criteria which is specified using the ON clause. Change the query as follows.

EXISTS
(SELECT CUS_DEMOGRAPHIC.CUSTOMER
FROM CUS_DEMOGRAPHIC INNER JOIN CUSTOMER
ON CUS_DEMOGRAPHIC.CUSTOMER = CUSTOMER.CO_MAIL_NAME
WHERE CUS_DEMOGRAPHIC.CODE = 'Corp') Terry
------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker