CODEWITHSUNDEEP
mysqlsql
Prikshit
Prikshit

Reputation: 13

Need to find specific records from two tables with SQL query

I've two tables

magento_customerbalance -mcb

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

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 the following SQL queries

Query #1:

SELECT COUNT(mcb.customer_id) 
FROM magento_customerbalance mcb;

Results :

total customer from mcb =121

Query #2:

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
  1. 121 -100 =21

customer who does not have attribute id 5 = 21

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

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

Upvotes: 0

Views: 62

Answers (2)

ahmed
ahmed

Reputation: 9181

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

Upvotes: 1

MUN FONG CHAN
MUN FONG CHAN

Reputation: 21

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;

Upvotes: 1

Related Questions