Question 21014 in Mysql Question posted in
The official MySQL documentation can be found here.

Need to find specific records from two tables with SQL query – Mysql

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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search