Simple Query Question Help

Question

Simple Query Question Help

beantownace

Old Hand

Points: 333
More actions
May 6, 2022 at 1:43 am

#4027724

Hello all,
I am stuck on something that should be very easy but struggling on this.
I have the following records in a table:
Customers
CustomerId MatchTypeId
1 1000
1 2000
I then am building a query dynamically but this is not working based on the IN statements.
I want to return the customer Id that matchtypeid has both 1000 and 2000 for rows.
Current query:
SELECT * FROM Customers
WHERE CustomerId = 1
AND (Customers.MatchTypeId IN (1000))
AND (Customers.MatchTypeId IN (2000))
So in this case it CustomerId 1 meets both the conditions but it is not working together like this. I would want to return 1 for example if the customer has both match type id's 1000 and 2000 otherwise nothing. Right now this returns nothing.
Thanks all this should be easy but I am blanking out on this one.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

I'd do this a bit differently. You need at least two tables. One for Customer, and the one you have above. (Preferably one more, that has details about what MatchTypeID is describing. Something like this maybe:

USE tempdb;
go

CREATE TABLE Customer(CustomerID INT PRIMARY KEY, CustomerName VARCHAR(20));
CREATE TABLE CustomerMatch(CustomerID INT, MatchTypeID INT);
GO
CREATE TABLE MatchType(MatchTypeID INT NOT NULL);
GO

INSERT INTO MatchType VALUES (1),(2),(3);

INSeRT INTO Customer(CustomerID, CustomerName) VALUES (1,'Al'),(2,'Bart'),(3,'Homer');
INSERT INTO CustomerMatch(CustomerID, MatchTypeID) VALUES (1,1),(1,2),(2,1),(3,2);

/*
I want to return the customer Id that matchtypeid has both 1000 and 2000 for rows.
*/
SELECT c.CustomerID
FROM Customer c
WHERE EXISTS (SELECT 1 
FROM CustomerMatch cm 
WHERE cm.CustomerID = c.CustomerID 
AND cm.MatchTypeID = 1)
AND EXISTS (SELECT 1 
FROM CustomerMatch cm 
WHERE cm.CustomerID = c.CustomerID 
AND cm.MatchTypeID = 2);

drew.allen SSC Guru Points: 76985 More actions · Answer 2

Your WHERE clause is evaluated for EACH RECORD. A single record cannot both be equal to 1000 and 2000. You need a query that looks at multiple records. Piet's is one approach, here is another that uses aggregates.

SELECT CustomerID
FROM Customers
GROUP BY CustomerID
HAVING MAX(CASE WHEN MatchTypeID = 1000 THEN 1 ELSE 0 END) = 1
   AND MAX(CASE WHEN MatchTypeID = 2000 THEN 1 ELSE 0 END) = 1

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

beantownace Old Hand Points: 333 More actions · Answer 3

Thank you both appreciate the help. Yes I need either two exists in this case or the max. Thanks

Ed B Hall of Fame Points: 3666 More actions · Answer 4

Do you ever have more then 2? This works for any number, you just need the count number to match the number of IN values. You said you were building the query dynamically and the only part that needs to change is the IN and the count number. Count distinct might not be efficient against a large table though.

DROP TABLE IF EXISTS dbo.Customers
CREATE TABLE dbo.Customers
  (CustomerID int,
    MatchTypeID int
 );

INSERT dbo.Customers (CustomerID, MatchTypeID)
VALUES (1, 1000), (1, 2000), (1, 3000), (2, 1000), (3, 2000), (4, 1000), (4, 3000);

SELECT CustomerID
FROM dbo.Customers
WHERE MatchTypeID IN (1000, 2000, 3000)
GROUP BY CustomerID
HAVING COUNT(DISTINCT MatchTypeID) = 3;

DROP TABLE IF EXISTS dbo.Customers;