Selecting same value from two columns in a lookup table

Question

question

umair975 asked Nov 30, '12

Selecting same value from two columns in a lookup table

I have a table as follows UserID CONSUMERID
1 2
1 3
1 4
5 1
this is a lookup column, user can be consumer here. I need to query the table and get a single column output so that for example if ID = 1 I should be able to query userid and get all consumnerid data where userid = 1 and at the same time query get all userid data where consumerid = 1.. all the results should come in one column. So in other words query both columns for 1 and return opposite column value.

sql query join

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-11-30T13:08:12Z

anthony.green answered Nov 30, '12

One way to do it with UNION declare @table table(userid int, consumerid int) insert into @table values (1,2),(1,3),(1,4),(5,1) select consumerid from @table where userid = 1 union select userid from @table where consumerid = 1

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey ♦♦ commented · Nov 30, 2012 at 01:12 PM

UNION will eliminate the duplicates, if any. Depends on what @umair975 is looking for I guess.

1 ·

Answer 2 · 2012-11-30T13:11:10Z

Grant Fritchey answered Nov 30, '12

How about something like this: SELECT mt.ConsumerId AS SingleColumn FROM dbo.MyTable AS mt WHERE mt.UserID = 1 UNION ALL SELECT mt.UserID AS SingleColumn FROM dbo.MyTable AS mt WHERE mt.ConsumerId = 1; Just substitute parameter values where I hard-coded the filter criteria.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question