CODEWITHSUNDEEP
sqlsasenterprise-guide
JaJa
JaJa

Reputation: 15

SQL Select rows with column values that match column values in different table

I have Table A that contains columns 'CustomerID' and 'MONTH', as well as a column 'PRODUCT' which is always the same e.g. Product X

I also have Table B that contains columns 'CustomerID' and 'MONTH' but this table includes lots of products, not just X.

I am trying to select rows from Table B (lots of products) whose 'CustomerID' AND 'MONTH' match a row in Table A, so that I end up with products that a customer bought in the same month as they bought Product X

I have tried the following:

PROC SQL;
   CREATE TABLE DATA.RESULT AS
   SELECT LOTS.Customer_ID, 
          LOTS.Product_Name, 
          LOTS.Order_Revenue, 
          LOTS.Month_Ordered
   FROM DATA.Family_Holiday_Orders_Plus AS LOTS
   INNER JOIN DATA.X AS X1 ON (X1.Customer_ID = LOTS.Customer_ID)
   INNER JOIN DATA.X AS X2 ON (X2.Month_Ordered = LOTS.Month_Ordered);
QUIT;

Upvotes: 1

Views: 2268

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

If I understand correctly you want to select all products from LOTS where another row in DATA.X with the same Customer_ID and Month_Ordered value exists.

SELECT LOTS.Customer_ID, 
       LOTS.Product_Name, 
       LOTS.Order_Revenue, 
       LOTS.Month_Ordered
FROM DATA.Family_Holiday_Orders_Plus AS LOTS
WHERE EXISTS (
    SELECT 1 FROM DATA.X AS X1 WHERE X1.Customer_ID = LOTS.Customer_ID
    AND X1.Month_Ordered = LOTS.Month_Ordered
)

Upvotes: 1

Related Questions