Link to home
Start Free TrialLog in
Avatar of sabecs
sabecs

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sabecs
sabecs

ASKER

Thanks angelIII.