Selecting values from second column alongside the values from first column in the same row-CodePudding

I am trying to get values matching the value from the second column. For example, I want to know who is the sender for Bill Gates by only using IDs.

I have two tables,

*users* table
| user_ID  | Full_name      |
| -------- | -------------- |
| 1        | Steve Jobs     |
| 2        | Bill Gates     |
| 3        | Elon Musk      |

*relationships* table (with both column foreign keys)
| user_sender  | user_receiver  |
| ------------ | -------------- |
| 1            | 2              |
| 3            | 1              |
| 3            | 2              |

I want to select based on "user_receiver" column the matching values in the column "user_sender"

For example, I want to know who is user_sender for 2 OUTPUT:

|              |                |
| ------------ | -------------- |
| 1            | 2              |
| 3            | 2              |

CodePudding user response：

You need to join the tables and select the rows you want

you have access to all columns of both tables by addressing them with their alias

SELECT u.user_ID  , u.Full_name,r.user_receiver 
 FROM users u JOIN 
relationships r ON u.user_ID = r.user_sender
WHERE r.user_receiver = 2

CodePudding user response：

If you want to look based on the name, then join the relationships to users.

SELECT 
  rel.user_sender
, rel.user_receiver
-- , sender.Full_name AS sender_name
-- , receiver.Full_name AS receiver_name
FROM relationships AS rel
JOIN users AS sender ON sender.user_ID = rel.user_sender
JOIN users As receiver ON receiver.user_ID = rel.user_receiver
WHERE receiver.Full_name = 'Bill Gates'

If you already know the user_receiver number, and you only want the ID's

SELECT *
FROM relationships
WHERE user_receiver = 2