July 12, 2018 at 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)
July 12, 2018 at 11:06 am
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)
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
July 12, 2018 at 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.
July 12, 2018 at 12:27 pm
souLTower - Thursday, July 12, 2018 11:49 AMIf 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:
| Person | Category | Item | qty |
|---|
| Joe | Books | Title1 | 3 |
| Joe | Books | Title2 | 0 |
| Joe | Furniture | Couch | 0 |
| Joe | Furniture | Table | 0 |
| Mary | Books | Title1 | 0 |
| Mary | Books | Title2 | 0 |
| Mary | Furniture | Couch | 0 |
| Mary | Furniture | Table 0 |
Saravanan
July 12, 2018 at 1:08 pm
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.
July 13, 2018 at 12:55 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply