SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Compare against multiple columns


Compare against multiple columns

Author
Message
souLTower
souLTower
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3276 Visits: 591
I 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)


souLTower
souLTower
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3276 Visits: 591
DoH. I can use exists and 'not exists'. I guess that's the better option.


SELECT person, category, item, Qty FROM Sales
UNION
SELECT person, category, item, 0 AS Qty FROM POSS
WHERE EXISTS
-- Only return the people and categories which have sales
(SELECT S.person from Sales S WHERE S.person = POSS.person AND S.category = POSS.category)
AND NOT EXISTS
-- Do not return rows which already exist in Sales
(SELECT S.person from Sales S WHERE S.person = POSS.person AND S.category = POSS.category AND S.item = POSS.item)






saravanatn
saravanatn
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1713 Visits: 1151
souLTower - Thursday, July 12, 2018 10:34 AM
I 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
souLTower
souLTower
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3276 Visits: 591
If you were to use inner join and the Mary sales record had 2 entries, each entry would join to the POSS table causing duplication. I don't think inner join is the way to go.
saravanatn
saravanatn
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1713 Visits: 1151
souLTower - Thursday, July 12, 2018 11:49 AM
If you were to use inner join and the Mary sales record had 2 entries, each entry would join to the POSS table causing duplication. I don't think inner join is the way to go.

I am getting exactly what your looking for. May be check your explanation and expected results.


with sales (Person,Category,Item,Qty)
as
(
select 'Joe','Books','Title1',3
union all
select 'Mary','Furniture','Couch',0
),

Poss (Person,Category,Item)
as
(
select'Joe','Books','Title1'
union all
select'Joe','Books','Title2'
union all
select'Mary','Books','Title1'
union all
select'Mary','Books','Title2'
union all
select'Joe','Furniture','Couch'
union all
select'Joe','Furniture','Table'
union all
select'Mary','Furniture','Couch'
union all
select'Mary','Furniture','Table'
)

select poss.*, case when sales.item=poss.item then qty else 0 end as qty from
sales
inner join
poss
on sales.Person=poss.Person
order by
poss.person,
poss.Category,
qty desc


Test Results:




Saravanan
souLTower
souLTower
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3276 Visits: 591
I see what you mean. If I look at the data from the POSS perspective instead of the Sales perspective that makes sense. Thanks, now to apply this to the real-world.
sgmunson
sgmunson
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80278 Visits: 6552
I think this will work as well:
SELECT
P.person,
P.category,
P.item,
ISNULL(SUM(S.Qty), 0) AS Qty
FROM POSS AS P
LEFT OUTER JOIN Sales AS S
ON P.person = S.person
AND P.category = S.category
AND P.item = S.item
GROUP BY
P.person,
P.category,
P.item
ORDER BY
P.person,
P.category,
P.item;



Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search