September 2, 2010 at 1:42 pm
I have a table of 'rules' to do pattern matching and a master table to report data from.
Some product keys have rules to apply, others simply have no rules.
Here is the sample data:
@t1 is the rules table
@Maintable is the master table
Problem: My sql code lost the rows where there are no rules to apply ( AX4, AX5, AX6)...
declare @t1 table (Productkey varchar(50), rank int, value int )
insert into @t1 values ('AAABBB' , 100, 1)
insert into @t1 values('AC' , 200, 1)
insert into @t1 values('AX1' , 300, 1)
insert into @t1 values('AMMMMMMMMMM' , 400, 0)
insert into @t1 values('Aoooooooooo' , 400, 0)
insert into @t1 values('APP' , 400, 0)
select * from @t1
declare @MainTable table(customerid int, ProductID int,
ProductKey varchar(50), groupID int)
insert into @MainTable values (100, 1000,'AMMMMMMMMMM', NULL)
insert into @MainTable values (100, 1013,'APP2', NULL)
insert into @MainTable values (100, 1005,'AC234', NULL)
insert into @MainTable values (100, 1006,'AC', 123)
insert into @MainTable values (200, 1004,'AC1234', NULL)
insert into @MainTable values (200, 2000,'AX1', 1)
insert into @MainTable values (200, 2002,'AX123', 2)
insert into @MainTable values (200, 2000,'AX1', 3)
insert into @MainTable values (200, 2007,'Aoooooooooo', 3)
insert into @MainTable values (300, 1004,'AC',NULL)
insert into @MainTable values (300, 2000,'AX4', 60)
insert into @MainTable values (300, 2001,'AX5', 70)
insert into @MainTable values (300, 2002,'AX6', 80)
select * from @MainTable
-- Rule 1 exclude all keys with value=0
select * from @MainTable M
left join @t1 T
ON M.ProductKey LIKE '%' + T.ProductKey + '%'
WHERE T.Value > 0
Desired output
customeridProductIDProductKeygroupIDProductkeyrankvalue
1001005AC234NULLAC2001
1001006AC123AC2001
2001004AC1234NULLAC2001
2002000AX11AX13001
2002002AX1232AX13001
2002000AX13AX13001
3001004ACNULLAC2001
3002000AX460
3002001AX570
3002002AX680
Thank you very much in advance!
September 2, 2010 at 2:31 pm
WHERE T.Value > 0 OR T.Value IS NULL
Or:
WHERE ISNULL( T.Value, 1) > 0
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 2, 2010 at 2:50 pm
Craig,
Cool! That works. Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply