MySQL table relationship problem

[arminebusch]

Hello,

I have the following table:
tableA (
intUser1,
intUser2
);

The values in the table are (1,2), (2,3) and (3,1).
I would like to select intUserIds from table who are associated with 2.
So in my case I would get the result 1 and 3.

I used a query saying:
SELECT intUserId1, intUserId2 FROM tableA WHERE intUserId1 = 2 OR intUserId2 = 2.
The problem is that this query always returns two columns, one of them being value 2. How can I select only the columns associated with 2?

Thank you in advance,
Armin

[jimbo_dk]

Try something like this:

SELECT intUserId1 FROM tableA WHERE intUserId2 = 2
UNION
SELECT intUserId2 FROM tableA WHERE intUserId1 = 2

[arminebusch]

Awesome. That certainly works.

However is there another alternative where I can do it with one query instead of two? I mean, the union of two selects performs two queries doesn't it?

Armin

[swampBoogie]

select case when IntuserId1 = 2 then Intuserid2 else IntuserId1 end
  from tableA
 where 2 in (IntuserId1,IntuserId2)

[arminebusch]

Awesome man.

I'll check it out.

Thanks for all the help

[arminebusch]

Hello,

I have another follow up question. This case has helped me retrieve the proper intUserId, but I need to now join this retrieved user id with other tables to get more data. When I tried this query, I get "Unknown column intFriendId"

select ( case when intUserId= 1 then intUserd1 else intUserId2 end ) intFriendId
from account a RIGHT JOIN relationship r
ON a.intUserId = intFriendId
where 1 in (intUserId1,intUserId2 )

I assigned an alias to the whole ( case ) statement. the alias is intFriendId and I'm trying to use that id to join other tables so I can retrieve the data that is related to intFriendId.

Thank You,
Armin

[swampBoogie]

select <columns>
  from relation as r left join
  (select 
    case when intUserId = 1 then intUserd1 
           else intUserId2 end) as intFriendId
     from account
   where 1 in (intUserId1,intUserId2)) as dt 
    ON r.intUserId = dt.intFriendId