CODEWITHSUNDEEP
mysqlsqldatabase
Wes
Wes

Reputation: 419

mysql - join select data from two different fields in table

I have two tables:

I'm trying to do one SQL query to join and pull all records that have the UserID or FriendID inside friends table equal to the user's ID but pull the FirstName and LastName from the other UserID.

For example

friends table

UserID = 1 | FriendID = 2
UserID = 3 | FriendID = 1

user table

UserID = 1 | FirstName = "Bob"  | LastName = "Hope"

UserID = 2 | FirstName = "John" | LastName = "Doe"

UserID = 3 | FirstName = "Bill" | LastName = "Murray"

If I am logged in as Bob(UserID = 1) trying to pull all of my friends user data (FirstName and LastName) in one query by checking if UserID 1 is either a FriendID or UserID inside the friends table. Then join the data for the opposite field that isn't my ID.

Any ideas?

Upvotes: 0

Views: 2165

Answers (3)

Billy Moon
Billy Moon

Reputation: 58521

If I understand your question, then this works

-- set the id of the logged in user
set @logged_in = 1;

-- select all the fields from the user table
select users.* from users
-- joined the friends table on the `FriendID`
inner join friends on friends.FriendID = users.UserID
-- filtered by `UserID` on friends table matching logged in user
and friends.UserID = @logged_in -- logged in id
-- union-ed with the users table
union select * from users
-- filtered by the `UserID` being the logged in user
where users.UserID = @logged_in -- logged in id

Results for @logged_in = 1:

UserID  FirstName   LastName
2       John        Doe
1       Bob         Hope

Results for @logged_in = 2:

UserID  FirstName   LastName
2       John        Doe

Test Database Create Code:

--
-- Table structure for table `friends`
--

CREATE TABLE IF NOT EXISTS `friends` (
  `UserID` int(11) NOT NULL,
  `FriendID` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `friends`
--

INSERT INTO `friends` (`UserID`, `FriendID`) VALUES
(1, 2),
(3, 1);

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `UserID` int(11) NOT NULL,
  `FirstName` varchar(50) NOT NULL,
  `LastName` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`UserID`, `FirstName`, `LastName`) VALUES
(1, 'Bob', 'Hope'),
(2, 'John', 'Doe'),
(3, 'Bill', 'Murray');

Upvotes: 1

Stephen Connolly
Stephen Connolly

Reputation: 14096

Select b.uid as userid, a.firstname, a.lastname 
from user a
Inner join (select friendid as uid from friends where userid=:currentUser
Union select userid as uid from friends where friendid=:currentUser) b

On a phone, so may need syntax tweaks.

An optimiser may suggest a different join strategy based on your real data

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

Try this:

SELECT *
FROM users u
WHERE userid IN ( SELECT userid FROM friends WHERE friendid = 1
                  UNION ALL
                  SELECT friendid FROM firends WHERE userid = 1);

This will give you:

| USERID | FIRSTNAME | LASTNAME |
---------------------------------
|      2 |      John |      Doe |
|      3 |      Bill |   Murray |

SQL Fiddle Demo

Upvotes: 1

Related Questions