MySQL - joining multiple tables

Hi,
How can I alter the SELECT query to only show id's for users that match my friends table.
For example if my id is "17" and my list of friends is produced from query "SELECT toid FROM `friends` WHERE fromid = '17'"

This would return 2012,2004,2002,2013,2006 which I then want to extract user information by joining 5 other tables.

CREATE TABLE IF NOT EXISTS `friends` (
  `id` int(11) NOT NULL auto_increment,
  `toid` int(11) NOT NULL,
  `fromid` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

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

INSERT INTO `friends` (`id`, `toid`, `fromid`) VALUES
(1, 2012, 17),
(2, 17, 2012),
(3, 2004, 17),
(4, 17, 2004),
(5, 2002, 17),
(6, 17, 2002),
(9, 2013, 17),
(10, 17, 2013),
(11, 2005, 2014),
(12, 2014, 2005),
(13, 2006, 2014),
(14, 2014, 2006),
(15, 2006, 17),
(16, 17, 2006);

I need the query below to display user information for the following id's 2012,2004,2002,2013,2006

SELECT u.id AS id,
u.sess_id AS sess_id,
u.username AS username,
u.session_last_active AS last_active,
u.email AS email,
u.contact AS contact,
profile.countryLive AS country,
profile.stateLive AS state,
profile.cityLive AS city,
profile.updated AS profile_upd,
profile.birthdate_day AS birthdate_day,
profile.birthdate_month AS birthdate_month,
profile.birthdate_year AS birthdate_year,
profile.gender AS gender,
profile.updated AS profile_upd,
personality.updated AS personality_upd,
photos.image1 AS image1,
photos.updated AS photos_upd,
video.updated AS video_upd FROM users AS u INNER JOIN
user_upd_personality AS personality,
user_upd_my_profile AS profile,
user_upd_photos AS photos,
user_upd_video AS video
WHERE profile.updated = 1
AND photos.updated = 1
AND id = personality.user_id
AND id = profile.user_id
AND id = photos.user_id
AND id = video.user_id
ORDER BY id
sabecsAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can do this with a condition like:

where id in ( SELECT toid FROM `friends` WHERE fromid = '17' )

hope this helps
0
 
sabecsAuthor Commented:
Thanks angelIII.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.