We help IT Professionals succeed at work.

MySQL - exclude certain records from results

sabecs
sabecs used Ask the Experts™
on
Hi,
Is it possible to modify my SQL statement below to exclude users from results if they match records found in my blocked user table?

For example if my id is 17, I dont want my details appearing if id's of 666 or 456 are performing the search?

$my_id = "17";
$their_id = "666";

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,
u.member_type AS member_type,
hobbies.updated AS hobbies_upd,
matchi.updated AS match_upd,
profile.countryLive AS country,
photos.image1 AS image1,
photos.updated AS photos_upd,
video.updated AS video_upd
FROM users AS u
INNER JOIN user_upd_hobbies_interest AS hobbies,
user_upd_match_info AS matchi,
user_upd_personality AS personality,
user_upd_my_profile AS profile,
user_upd_photos AS photos,
user_upd_video AS video
WHERE
id = hobbies.user_id
AND id = matchi.user_id
AND id = personality.user_id
AND id = profile.user_id
AND id = photos.user_id
AND id = video.user_id
AND profile.updated = '1'
ORDER BY member_type DESC, photos_upd DES


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

--
-- Dumping data for table `block_user`
--

INSERT INTO `block_user` (`id`, `toid`, `fromid`) VALUES
(22, 29, 567),
(23, 666, 17),
(24, 456, 17);
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
and not (id = $my_id and (select count(1) from block_user where toid = $their_id and fromid = $my_id) > 0)

Author

Commented:
Thanks Sweetfa2,
Sorry I think I have confused my self, If I am logged as my_id 17, then I want to exclude 666 & 456 from results?
($their_id wont be set to anything)
Commented:
and id not in (select distinct toid from block_user where fromid = $my_id)

Author

Commented:
Thanks sweetfa2, its perfect.