August 27, 2004 at 7:06 pm
I am stuck with a query problem. I have two tables whose structure is described below.
If the attributename in @fulltable matches the attributename in @subtable then compare the correponding value columns and return the rows whose values match. If the attributename does not match, return the row anyway.
set nocount on
declare @fulltable table
(itemid int,
attributename varchar(100),
value int)
declare @subtable table
(attributename varchar(100),
value int)
insert into @fulltable values (1, 'a', 100)
insert into @fulltable values (1, 'b', 200)
insert into @fulltable values (1, 'c', 300)
insert into @fulltable values (2, 'a', 400)
insert into @fulltable values (2, 'b', 500)
insert into @subtable values ('a', 100)
insert into @subtable values ('b', 500)
In the SQL above, my result should return
1 'a' 100 -- Because the value matches
1 'c' 300 -- Because the attributename is not present in @subtable
2 'b' 500 -- Because the value matches
-- 1 'b' 200 and 2, 'a', 400 should be eliminated as the attributenames in @fulltable match the attributenames in @subtable, but the values dont.
I have tried something similar to:
SELECT ft.*
FROM @fulltable ft
INNER JOIN @subtable sub
ON ft.value = CASE WHEN ft.attributename = sub.attributename
THEN sub.value
ELSE ft.value
END
As you can see, it does not return the desired result. Please let me know if you need any further explanation of what I am trying to achieve. Any help is greatly appreciated.
August 29, 2004 at 12:01 pm
Hi,
My suggestion would be to rewrite and use union. But of course, the performance of the query below is not goint to impress anybody...
SELECT ft.*
FROM @fulltable ft
INNER JOIN @subtable sub
ON ft.attributename = sub.attributename
AND ft.value = sub.value
UNION
SELECT ft.*
FROM @fulltable ft
WHERE ft.attributename NOT IN (SELECT attributename FROM @subtable)
August 29, 2004 at 12:53 pm
Olavho-
I greatly appreciate your time. There is a little bit more to the problem. Let me explain.
ValueColumnName in the @subtable gives the name of the column in the @fulltable with which the value in the @subtable should be compared. I have tried many different ways but still cannot get the solution to this problem. I greatly appreciate if you could help me with this query.
set nocount on
declare @fulltable table
(itemid int,
attributename varchar(100),
valueInt int,
valueString varchar(256),
valueFloat float)
declare @subtable table
(attributename varchar(100),
value varchar(256),
ValueColumnName varchar(100))
insert into @fulltable values (1, 'a', NULL, 'String1', NULL)
insert into @fulltable values (1, 'b', 200, NULL, NULL)
insert into @fulltable values (1, 'c', NULL, NULL, 1.31)
insert into @fulltable values (2, 'a', NULL, 'String2', NULL)
insert into @fulltable values (2, 'b', 500, NULL, NULL)
insert into @subtable values ('a', 'String1', 'ValueString')
insert into @subtable values ('b', 500, 'ValueInt')
The ouput I am expecting is still similar as before.
1 'a' NULL 'String1' NULL -- Because the value matches
1 'c' NULL NULL 1.31 -- Because the attributename is not present in @subtable
2 'b' 500 NULL NULL -- Because the value matches
August 30, 2004 at 2:07 am
Sorry about my misunderstanding. Kind of tricky problem, and I'm afraid I can't give you a good solution.
My best attempt (it seems to work for your example, but is probably not as dynamic as you would like):
SELECT ft.*
FROM @fulltable ft
INNER JOIN @subtable sub ON ft.attributename = sub.attributename
AND sub.value = CASE WHEN sub.ValueColumnName = 'ValueInt'
THEN convert(varchar(256), ft.ValueInt)
WHEN sub.ValueColumnName = 'ValueString'
THEN convert(varchar(256), ft.ValueString)
WHEN sub.ValueColumnName = 'ValueFloat'
THEN convert(varchar(256), ft.ValueFloat)
END
UNION
SELECT ft.*
FROM @fulltable ft
WHERE NOT EXISTS (SELECT 1 FROM @subtable sub WHERE ft.attributename = sub.attributename)
ORDER BY ft.itemid
August 30, 2004 at 8:40 am
Try this. You may need to deal with cleanup issues with the float column if the data is predictable.
SELECT
f.itemid,
f.attributename,
CASE WHEN (s.attributename IS NULL) OR (s.ValueColumnName <> 'ValueInt') THEN f.valueInt
ELSE CONVERT(INT, s.value) END AS valueInt,
CASE WHEN (s.attributename IS NULL) OR (s.ValueColumnName <> 'ValueString') THEN f.valueString
ELSE s.value END AS valueString,
CASE WHEN (s.attributename IS NULL) OR (s.ValueColumnName <> 'ValueFloat') THEN f.valueFloat
ELSE CONVERT(FLOAT, s.value) END AS valueFloat
FROM @fulltable f
LEFT JOIN @subtable s ON s.attributename = f.attributename
WHERE (s.attributename IS NULL)
OR ((f.attributename = s.attributename)
AND ( CONVERT(VARCHAR, s.value) = (CASE WHEN s.ValueColumnName = 'ValueString' THEN CONVERT(VARCHAR, f.valueString)
WHEN s.ValueColumnName = 'ValueInt' THEN CONVERT(VARCHAR, f.valueInt)
WHEN s.ValueColumnName = 'ValueFloat' THEN CONVERT(VARCHAR, f.valueFloat)
END)))
RESULTS
itemid attributename ValueInt ValueString ValueFloat
----------- --------------- ----------- ------------ -------------------
1 a NULL String1 NULL
1 c NULL NULL 1.3100000000000001
2 b 500 NULL NULL
August 30, 2004 at 12:32 pm
Simplify:
select a.itemid, a.attributename, a.value
from @fulltable a
LEFT OUTER JOIN @subtable b ON a.attributename = b.attributename
where b.attributename is null
OR a.value = b.value
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply