Find the customer_IDs of the customers who-CodePudding

My code is:

CREATE TABLE `table_a` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `value` varchar(255),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `table_b` LIKE `table_a`;

INSERT INTO table_a VALUES (1, 'A'), (2, 'B'), (3, 'B');
INSERT INTO table_b VALUES (1, 'B');

SELECT value FROM table_a
INNER JOIN table_b
USING (value);

CodePudding user response：

I would use aggregation here:

SELECT c.customer_ID 
FROM customer c
INNER JOIN transactions t
    ON t.customer_ID = c.customer_ID
INNER JOIN transaction_contains tc
    ON tc.transaction_ID = t.transaction_ID
INNER JOIN product p
    ON tc.UPC = p.UPC
WHERE
    p.brand IN ('Pepsi', 'Coca-Cola')
GROUP BY
    c.customer_ID
HAVING
    COUNT(DISTINCT p.brand) = 2;

Note that I also used modern explicit inner joins.

CodePudding user response：

Something like this should just about do it.

SELECT
    DISTINCT(c1.Customer_ID)
FROM
    Customer c1
JOIN Transactions t ON t.Customer_ID = c1.Customer_ID
JOIN Contain c2 ON c2.Transaction_ID = t.Transaction_ID
JOIN Products p ON p.UPC = c2.UPC
WHERE
    p.Brand IN ('Pepsi', 'Coca-Cola');

CodePudding user response：

You can achieve this by using group by. If you select 2 products in IN, then your count must be 2 . And I used ANSI SQL standard syntax for joins

select  
  C1.customer_ID 
from 
  customer as C1 inner join 
  transactions as T1 on C1.customer_ID = T1.customer_ID  inner join 
  transaction_contains as T2 on T1.transaction_ID = T2.transaction_ID inner join
  product P on p.upc = T2.upc  
where 
  p.Brand IN ('Pepsi', 'Coca-Cola')
Group by 
  C1.customer_ID 
having 
  count(*) = 2

syntax is checked against syntax validator