Home Forums SQL Server 2008 SQL Server 2008 - General Compare against multiple columns RE: Compare against multiple columns
July 12, 2018 at 11:40 am
souLTower - Thursday, July 12, 2018 10:34 AMI have a table representing sales. I want to return a list of sales along with the possible sales.
-- table Sales
Person Category Item Qty
-------------------------------------------------------------
Joe Books Title 1 3
Mary Furniture Couch 2-- Query representing all possible people, categories, and items
-- Call it Poss
Person Category Item
--------------------------------------------------
Joe Books Title 1
Joe Books Title 2
Mary Books Title 1
Mary Books Title 2
Joe Furniture Couch
Joe Furniture Table
Mary Furniture Couch
Mary Furniture Table
Bob Books Title 1
Bob Books Title 2
Bob Furniture Couch
Bob Furniture Table-- What I want is this. Note that Bob is not in the results
Person Category Item Qty
----------------------------------------------------------
Joe Books Title 1 3
Joe Books Title 2 0
Joe Furniture Couch 0
Joe Furniture Table 0
Mary Books Title 1 0
Mary Books Title 2 0
Mary Furniture Couch 1
Mary Furniture Table 0-- What I have is this, combining the various combinations into a unique key representing each possible row.
-- I feel there should be a better way to do this but I'm drawing a blank on how to compare across multiple columns. This approach
-- causes a concatenation to be made for every row which is not very efficient.SELECT person, category, item, Qty FROM Sales
UNION
SELECT person, category, item, 0 AS Qty FROM POSS
WHERE
-- Only return the people and categories which have sales
person+category IN
(SELECT person+category FROM Sales)
AND
-- Do not return rows which already exist in Sales
person+category+item NOT IN
(SELECT person+category+item FROM Sales)
I think what you want is Inner Join on Person column and Category Column in both sales and Poss Tables.
Any one correct me if I am wrong
Saravanan