I have 2 columns in a table as such;

(probably messed up the table description, but)

Anyway, I want to query columns A and B, for say 222 but pull the corresponding data from the other column. A contact link, where one or the other members send the request and the other agrees, hence the 1 and 1 in columns UA, UB. But I need the corresponding column to join the other users information on, so I can be displayed in a list of contacts.

I can do a simple query to pull each row where a users # is, but can't figure out how to get the other user as the actual data I want.

Hope that makes sense.

Thank you

CodePudding user response：

Will the ColA be the user id number for the first person, and ColB be the user id number for the second person?

CodePudding user response：

Your basic approach should be to flatten out your data so you can treat it like a normal query. You don't specify a RDBMS, but this works for SQL-Server

--Set up sample data
;with cteTabA as ( 
    SELECT * FROM ( VALUES 
        ('111', '222', '1', '2')
        , ('222', '333', '11', '21')
        , ('222', '444', '12', '22')
        , ('111', '333', '13', '33')
        , ('555', '222', '14', '41')
    ) as TabA(ColA, ColB, UA, UB) 
) SELECT * 
INTO #Sample
FROM cteTabA

--SELECT * FROM #Sample --Display test data
--Set up what we're looking for
DECLARE @LookFor char(3) = '222';

--Solution - flatten the table, but log where the ID came from
with cteFlat as (
    SELECT ColA as ID, UB as Opposite, 'A' as MatchCol
    FROM #Sample
    UNION ALL 
    SELECT ColB as ID, UA as Opposite, 'B' as MatchCol
    FROM #Sample
)--Now just search for it
SELECT * FROM cteFlat as F 
WHERE ID = @LookFor 

--Clean up 
DROP TABLE #Sample 

Output: