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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER