Find the customer_IDs of the customers who

Question

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);

`P.brand` can never be equal to Pepsi and Coke at the same time, so the last line of your WHERE is logically impossible. Also, using commas in your FROM statement has been obsolete for 30 years now - use a proper JOIN. If you learned the syntax you're using from a book, throw it away and find another one. If you got it from an online tutorial, find a different one. If you learned it from your teacher, buy them a newer book. — Ken White, Oct 21 '22 at 23:29

score 1 · Answer 1 · answered Oct 21 '22 at 23:39

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.

score 0 · Answer 2 · answered Oct 21 '22 at 23:35

0

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');

answered Oct 21 '22 at 23:35

NinjaScareCrow

94
1
6

1

This answer is totally _wrong_, and will always return empty set. A record can never simultaneously belong to 2 different brands. This is the same incorrect logic used by the OP. – Tim Biegeleisen Oct 21 '22 at 23:54
@TimBiegeleisen You are in fact correct... my apologies >.< Been a long day O.o Ignore this answer :P – NinjaScareCrow Oct 22 '22 at 00:07

T.S. · Answer 3 · 2022-10-21T23:55:51.167

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

Find the customer_IDs of the customers who

3 Answers3