Selecting same value from two columns in a lookup table
I have a table as follows UserID CONSUMERID
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.
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
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.