January 14, 2009 at 9:25 am
I have a table which contains a list of categories.
Each category has a description.
We want our customers to be able to customize their descriptions, so we have added a CustomerID to the table.
If there is a customer-specific description we will add another row with the CustomerID set.
All other customers should see the default description.
I'm having trouble figuring out how to write the query.
Here is some sample DDL & DATA:
-- create a sample table to play with
-- if the customer id is null it applies to all customers
-- if the customer id is set this is a customer-specific description
DECLARE @Categories TABLE
(
CategoryID INT
,Description VARCHAR(20)
,CustomerID INT
)
-- insert some sample data
INSERT INTO @Categories(CategoryID, Description, CustomerID)
SELECT 1, 'Cars', NULL
UNION ALL SELECT 2, 'Trucks', NULL
UNION ALL SELECT 3, 'Boats', NULL
UNION ALL SELECT 1, 'Automobiles',1
SELECT *
FROM @Categories
/*
-- here are the results I am expecting for Customer 1:
CategoryIDDescriptionCustomerID
1Automobiles1
2TrucksNULL
3BoatsNULL
-- here are the results I am expecting for Customer 2:
CategoryIDDescriptionCustomerID
1CarsNULL
2TrucksNULL
3BoatsNULL
*/
January 14, 2009 at 1:05 pm
Solved it! Thanks to some help from a co-worker.
DECLARE @Results TABLE
(
CategoryID INT
,Description VARCHAR(20)
)
INSERT INTO @Results
SELECT CategoryID, Description
FROM @Categories
WHERE CustomerID IS NULL
UPDATE R
SET R.Description = C.Description
FROM @Results R
JOIN @Categories C ON R.CategoryID = C.CategoryID
WHERE C.CustomerID = 1
SELECT *
FROM @Results
January 14, 2009 at 2:10 pm
Hi,
I see you've solved this now but here is the answer I came up with....
declare @customerID int
set @customerID = 2
SELECT * FROM @Categories where CustomerID =@customerID
union all
SELECT * FROM @Categories where CustomerID is null and CategoryID not in (SELECT CategoryID FROM @Categories where CustomerID =@customerID)
B
January 14, 2009 at 2:15 pm
I would seriously rethink this solution. Customer-generated descriptions should, really, be in a separate sub-table.
Otherwise, you're likely to end up with two or more descriptions for the same item with the same customer ID, and so on.
You'll get better performance out of it too, and simpler queries.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 2:31 pm
GSquared (1/14/2009)
I would seriously rethink this solution. Customer-generated descriptions should, really, be in a separate sub-table.Otherwise, you're likely to end up with two or more descriptions for the same item with the same customer ID, and so on.
You'll get better performance out of it too, and simpler queries.
I do have some room to change the current design.
How would you suggest designing the second table?
January 14, 2009 at 2:36 pm
B (1/14/2009)
Hi,I see you've solved this now but here is the answer I came up with....
declare @customerID int
set @customerID = 2
SELECT * FROM @Categories where CustomerID =@customerID
union all
SELECT * FROM @Categories where CustomerID is null and CategoryID not in (SELECT CategoryID FROM @Categories where CustomerID =@customerID)
B
Smart one.
Thanks!
January 14, 2009 at 2:58 pm
If you assigned a "default" customer ID other than NULL, you could then enforce a unique constraint (to prevent duplicate customer+cat entries)
As for me - I'd look at:
;With CatInnerCTE as
(select categoryID,
case when customerID=@customerID then 1 else 2 end CatRank,
catDescription
from @categories
where customerID is null OR customerID=@customerID),
CatOuterCTE as
(select rowcount() over (Partition by CAtegoryid order by catrank) RN,
categoryID,
catDescription
from CatInnerCTE )
Select *
from CatOuterCTE
where RN=1
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2009 at 3:10 pm
I'd have one table with the item ID and it's default description, and another table with the PK on item ID and customer ID, and the customer-specific description. Then a left join does all the work for you.
create table dbo.Items (
ItemID int identity primary key,
Description varchar(100));
create table dbo.ItemsCustomerDescriptions (
ItemID int not null,
CustomerID int not null,
constraint PK_ItemsCustomerDecriptions primary key (ItemID, CustomerID),
CustomerDescription);
select Items.ItemID, isnull(CustomerDescription, Description) as Description
from dbo.Items
left outer join dbo.ItemsCustomerDescriptions
on Items.ItemID = ItemsCustomerDescriptions.ItemID
and CustomerID = @CustomerID;
Having multiple rows for the same entity in the same table is, pretty much, a violation of the whole reason to have relational databases in the first place.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 15, 2009 at 6:29 am
i ended up doing it a different way...i got a sub select of all default values cross joined against the customers, and then used a COALESCE to get the updated descriptiosn if it existed:
[font="Courier New"]
-- create a sample table to play with
-- if the customer id is null it applies to all customers
-- if the customer id is set this is a customer-specific description
DECLARE @Categories TABLE
(
CategoryID INT
,Description VARCHAR(20)
,CustomerID INT
)
-- insert some sample data
INSERT INTO @Categories(CategoryID, Description, CustomerID)
SELECT 1, 'Cars', NULL
UNION ALL SELECT 2, 'Trucks', NULL
UNION ALL SELECT 3, 'Boats', NULL
UNION ALL SELECT 1, 'Automobiles',1
UNION ALL SELECT 1, 'Automobiles',2
UNION ALL SELECT 1, 'Cars And Crossovers',3
UNION ALL SELECT 2, 'Trucks and SUVs',3
SELECT * FROM @CATEGORIES
--this gives a cross join of the default definition for every customer
SELECT
ORIG.CategoryID,
ORIG.Description,
MYCUST.CustomerID
FROM (SELECT CategoryID,Description FROM @CATEGORIES WHERE CustomerID IS NULL) ORIG
CROSS JOIN (SELECT DISTINCT CustomerID FROM @CATEGORIES WHERE CustomerID IS NOT NULL) MYCUST
--this gives the original combined with updated descriptions for every CustomerID
SELECT
MYDEFINITIONS.CategoryID,
COALESCE(FINAL.DESCRIPTION,MYDEFINITIONS.DESCRIPTION) AS DESCRIPTION,
MYDEFINITIONS.CUSTOMERID
FROM (
SELECT
ORIG.CategoryID,
ORIG.Description,
MYCUST.CustomerID
FROM (SELECT CategoryID,Description FROM @CATEGORIES WHERE CustomerID IS NULL) ORIG
CROSS JOIN (SELECT DISTINCT CustomerID FROM @CATEGORIES WHERE CustomerID IS NOT NULL) MYCUST
) MYDEFINITIONS
LEFT OUTER JOIN (
SELECT *
FROM @CATEGORIES CUST
WHERE CUST.CUSTOMERID IS NOT NULL) FINAL
ON FINAL.CATEGORYID = MYDEFINITIONS.CATEGORYID
AND FINAL.CustomerID = MYDEFINITIONS.CustomerID
ORDER BY MYDEFINITIONS.CustomerID ,MYDEFINITIONS.CATEGORYID [/font]
Lowell
January 15, 2009 at 2:50 pm
GSquared (1/14/2009)
I'd have one table with the item ID and it's default description, and another table with the PK on item ID and customer ID, and the customer-specific description. Then a left join does all the work for you.Having multiple rows for the same entity in the same table is, pretty much, a violation of the whole reason to have relational databases in the first place.
Makes a lot of sense to me.
Unfortunately I was not able to convince my boss to change the design. 🙁
I'll have to stick to one of the other solutions.
Thanks to everyone who helped out with this one!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply