sql where clause condition not working as expected.need only specific tag records-CodePudding

This my request, I have to get customer report that should include CUST_ID(primary key),PHONE_NUM(mandatory),EMAIL(optional),ATTRIBUTE_VALUE(mandatory) BY joining 3 tables. The report should consist of the only registered customers who has ATTRIBUTE_VALUE = 'REG' and anything. but the ATTRIBUTE_VALUE should not have 'UNREG'and 'GUEST';

table name : CST_CUST_ATTRIBUTES

cust_id	attribute_value
First	row
Second	row

table name : CST_CUST_PHONE

cust_id	PHONE_NUM
First	row
Second	row

table name : CST_EMAIL

cust_id	EMAIL_ADDR
First	row
Second	row

Below is my query. The joins parts are fine but where clause conditioning is not working as expected. I validated the records in a random.'UNREG' and 'GUEST' ATTRIBUTE_VALUE customers also gets included in the record.

SELECT DISTINCT
   A.CUST_ID,
   B.PHONE_NUM,
   C.EMAIL_ADDR,
   A.ATTRIBUTE_VALUE 
FROM
   CST_CUST_ATTRIBUTES A 
   INNER JOIN
      CST_CUST_PHONE B 
      ON A.CUST_ID = B.CUST_ID 
   LEFT JOIN
      CST_EMAIL C 
      ON B.CUST_ID = C.CUST_ID 
WHERE
   A.ATTRIBUTE_VALUE = 'REG' 
   and A.ATTRIBUTE_VALUE not in 
   (
      'UNREG',
      'GUEST'
   )

iam using oracle pl/sql developer 12.kindly need help in the expected where condition.

Thank you in Advance :)

CodePudding user response：

Case sensitivity might be an issue here. You may give a try to -

AND UPPER(A.ATTRIBUTE_VALUE) NOT IN ('UNREG', 'GUEST');

CodePudding user response：

I suspect where clause should be something like this:

where a.attribute_value = 'REG'
  and not exists (select null
                  from cst_cust_attributes x
                  where x.cust_id = a.cust_id
                    and x.attribute_value in ('UNREG', 'GUEST')
                 )

Though, it is quite difficult to test as sample data you posted is rather poor. "row"? Where are "REG" and "GUEST"?