How to get all non deleted messages from one user I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like:

CREATE TABLE IF NOT EXISTS `default_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `message` text COLLATE utf8_unicode_ci NOT NULL,
  `sender_user_id` int(11) NOT NULL,
  `reply_to_message_id` int(11) NOT NULL,
  `thread_root_message_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;


And this is the DATA for this table:

INSERT INTO `default_messages` (`id`, `subject`, `message`, `sender_user_id`, `reply_to_message_id`, `thread_root_message_id`, `date`, `deleted`) VALUES
(1, 'asdasdadasdasdasdasd', 'sdasdasdad', 1, 0, 0, '2012-05-11 09:29:00', 1),
(2, 'sadasdasd', 'asdasd', 2, 0, 0, '2012-05-11 09:44:23', 0),
(3, 'Re: asdasdadasdasdasdasd', 'asdadadadadadad', 2, 1, 0, '2012-05-11 09:44:41', 1),
(4, 'Re: sadasdasd', 'asdadad respuesta', 1, 2, 0, '2012-05-11 09:46:22', 1);


Now for the second table this is the SQL:

CREATE TABLE IF NOT EXISTS `default_recipient` (
  `message_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `read` int(11) NOT NULL DEFAULT '0',
  `recipient_read_date` datetime DEFAULT NULL,
  `deleted` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


And the DATA is this one:

INSERT INTO `default_recipient` (`message_id`, `user_id`, `read`, `recipient_read_date`, `deleted`) VALUES
 (1, 2, 1, NULL, 0),
 (2, 1, 1, NULL, 1),
 (3, 1, 1, NULL, 1),
 (4, 2, 1, NULL, 1);


Now I have this query for get all messages for user_id=x where deleted columns in both tables default_recipient and default_messages is not equal to 1 but it's not working because I get two results instead of one when user_id at table recipient or sender_user_id at table messages takes value equal 2:

SELECT p.*, rcp.*, msg.*, msg.id as message_id FROM default_messages msg 
        LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
        LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
        WHERE msg.sender_user_id = 2 OR rcp.user_id = 2
        AND msg.deleted = 0 AND rcp.deleted = 0
        ORDER BY msg.date DESC


What I'm doing wrong?

EDIT: The right question here was: how to get all messages for one user "X" let says where rcp.deleted = 0 OR msg.deleted = 0
AND has higher precedence than OR. Your condition is parsed and executed as:

    WHERE (msg.sender_user_id = 2) 
       OR ( rcp.user_id = 2
        AND msg.deleted = 0 
        AND rcp.deleted = 0
          )


Perhaps you mean to write:

    WHERE (msg.sender_user_id = 2 OR rcp.user_id = 2)
      AND msg.deleted = 0 
      AND rcp.deleted = 0