Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Unanswered: 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.

  2. #2
    Join Date
    Mar 2004
    Posts
    5
    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 ..

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    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

  4. #4
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    This should help:

    Code:
    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
    Davide Mauri
    http://www.davidemauri.it

  5. #5
    Join Date
    Mar 2004
    Posts
    5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •