Group Chat

For this challenge, suppose we are working on an app similar to Facebook Messenger, called "Footbook Messenger".

The Footbook users are in a table called fb_users. Of course, people can add other users as friends. The friendships are a junction table called friendships.

One important difference for this junction table compared to our Tweeter example is that friendships are always mutual. For Tweeter, people followed one another. One person may follow another without them following back. If they do follow back, that is a separate row in the junction table.

Now, consider friendships. If user ID 100 is friends with user ID 101, whose ID goes in the friend_id_1 column and whose in friend_id_2? When you join the tables, you need to know which ID goes where to write your join condition. There are multiple ways to solve this. One simple way is to just add both rows. So, when user 100 friends 101, just add both (100, 101) and (101, 100) as rows in the friendships table. This makes writing queries easier. That's how this example is done.

We have yet another table for our app: phone_contacts. These are for the imported contacts that the Messenger app asks for when you install it on your phone.

We are working on building a group chat feature for our Messenger app. The feature allows you to have group chats with your friends on Footbook and also your contacts on your phone. The app will reach your Footbook friends via their username, and your phone contacts via their phone number.

Write a query that will fetch a list of potential group chat members for the user with username Reyes22. You'll need to fetch both her friends from the fb_users table and the contacts in the phone_contacts table. Your results should have 2 columns:

1. A column called contact_info which is the username if the candidate is a Footbook Friend or a phone number if it's a phone contact.
2. A column called source which is the name of the table each candidate came from, 'friends' or 'phone_contacts'.