Need to find specific records from two tables with SQL query-CodePudding

I've two tables

A.)magento_customerbalance -mcb

coulumn -
1. balance_id
2. customer_id
3. website_id
4. amount
5. base_currency_code

B.)customer_entity_varchar -cev

column - 
1. value
2. attribute_id
3. entity_id
4. value

I've tried to find customer_id from mcb which does not have cev.attribute_id 5 with following sql queries

SELECT  count(mcb.customer_id) FROM magento_customerbalance mcb;

results :
total customer from mcb =121

SELECT  mc.customer_id FROM magento_customerbalance mc inner join customer_entity_varchar 
    cev on cev.entity_id = mc.customer_id
    where cev.attribute_id = 5;

results :
    total customers from mcv with attribute id 5 = 100

121 -100 =21

customer who does not have attribute id 5 = 21

How we can get these 21 mcb.customer_id records with sql query?

In cev table there are multiple attribute ids for same customer.

CodePudding user response：

Use the not exists operator with a correlated subquery as the following:

SELECT mcb.customer_id /* add other columns as needed */
FROM magento_customerbalance mcb
WHERE NOT EXISTS
(
  SELECT 1 FROM customer_entity_varchar cev
  WHERE cev.entity_id = mcb.customer_id AND
        cev.attribute_id = 5
)

And if you want only customer_ids which have an entity_id in the cev table add this condition:

AND EXISTS 
(
  SELECT 1 FROM customer_entity_varchar cev
  WHERE cev.entity_id = mcb.customer_id 
)

See demo

CodePudding user response：

Did you tried not equal to 5, !=5 ?

SELECT  mc.customer_id FROM magento_customerbalance mc inner join customer_entity_varchar 
    cev on cev.entity_id = mc.customer_id
    where cev.attribute_id != 5 OR cev.attributee IS NULL;