skip to Main Content

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

3

Answers


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

    Login or Signup to reply.
  3. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search