Help with a query

Hello all-

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.May be this can be the solution;

Try to write two different queries after this use them together ..

Query q1

SELECT fulltable.attributename, fulltable.value
FROM fulltable INNER JOIN
subtable ON fulltable.attributename = subtable.attributename AND fulltable.value = subtable.value

This will give
a 100
b 500

Query q2

SELECT fulltable.attributename, fulltable.value
FROM fulltable LEFT OUTER JOIN
subtable ON fulltable.attributename = subtable.attributename
WHERE (subtable.attributename IS NULL)

This will result
c 300

Query q3

SELECT * FROM q1 UNION SELECT * FROM q2

This will result
a 100
b 500
c 300

Hope this will solve your problem ..|||Sishe-

Your solution works great. 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|||This should help:

select
f.*
from
@.fulltable f
left outer join
@.subtable s
on
s.value = case
when s.ValueColumnName = 'ValueInt' then cast(f.valueInt as varchar(100))
when s.ValueColumnName = 'ValueString' then f.valueString
when s.ValueColumnName = 'ValueFloat' then cast(f.valueFloat as varchar(100))
end
where
s.value is not null

union all

select
f.*
from
@.fulltable f
left outer join
@.subtable s
on
f.attributename = s.attributename
where
s.value is null

order by
f.itemid, f.attributename|||manowar did give the solution.. One thing I didn't see in first query is please use "UNION ALL" not "UNION" in first query. Because UNION works like DISTINCT keyword so it may cause wrong result sets.