advice on 2-field query

(cross posted at sitepoint... sorry)

I have a table which stores related items
(ie, Acme Shirt matches with Acme Pants)

it's set up very simple-

id, sku1, sku2


The match can work both ways. ie, if i'm browsing the pants, the top should show, and if i'm browsing tops, the pant should show.

So, i need to check both fields for an instance of a matching item, and thus its coordinating item in the opposite field

i'm thinking about using a nested union... something like this:

select sku1, sku2 from table where id in (
select id from table where sku1 = 'pantsku'
union 
select id from table where sku2 = 'pantsku'
)

Code (markup):

then, in the scripting:

if not rs.eof then
  if pantsku = rs("sku1") then 
    '  use sku2 for matching item
    xsuccess = relateditem(rs("sku2"))
  else
    '  use sku1 for matching item
      xsuccess = relateditem(rs("sku1"))
  end if
end if
'-----------
' show related product from function call here...

Code (markup):

This has some downfalls... namely, if there's more than one match (poor data entry will happen, i'm sure)

i'm not sure that this is the best way to go about it.

any other ideas?


if it maters:
DBMS: MS SQL 2000
Script: classic ASP

 

If you need both columns and want to filter doubles then do at the first line
SELECT DISTINCT sku1, sku2 from ...

More easy solution as from what I understand the key value you search on in col1 or col2 is the same and you don't need to know if the related value comes out of col1 or col2 ?

Then do:
select distinct sku2 from table where sku1 = 'pantsku'
union
select distinct sku1 from table where sku2 = 'pantsku'