Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
+1 for the edit with the data. Thanks for that. –  JNK May 15 '12 at 13:53
    
why don't you use group by at the end of the query? –  jcho360 May 15 '12 at 13:57
    
@jcho360 group as GROUP BY msg.sender_id? Meaning GROUP BY id because in both tables have to be the same? That is your suggestion? –  ReynierPM May 15 '12 at 13:59
    
@jcho360 that wouldn't solve anything... –  JNK May 15 '12 at 14:00
    
I'm not getting the error, sorry, I thought that you are receiving 2 times every columns –  jcho360 May 15 '12 at 14:05
show 4 more comments

3 Answers 3

up vote 1 down vote accepted

Two suggestions

SUGGESTION #1 : Refactor the Query

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

SUGGESTION #2 : Index the tables to support the subqueries

ALTER TABLE default_messages ADD INDEX (sender_user_id,deleted,date,id);
ALTER TABLE default_recipient ADD INDEX (user_id,deleted);

As an example, I loaded the data from your question and indexed the two tables

mysql> use reynierpm
Database changed
mysql> drop table if exists default_messages;
Query OK, 0 rows affected (0.05 sec)

mysql> drop table if exists default_recipient;
Query OK, 0 rows affected (0.03 sec)

mysql> 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 ;
Query OK, 0 rows affected (0.13 sec)

mysql> 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;
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE default_messages ADD INDEX (sender_user_id,deleted,date,id);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE default_recipient ADD INDEX (user_id,deleted);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 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);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->     rcp.*, msg.*, msg.id as message_id
    -> FROM
    -> (
    ->     SELECT id FROM default_messages
    ->     WHERE sender_user_id = 2 AND deleted = 0
    ->     ORDER BY date DESC
    -> ) msgkey
    -> LEFT JOIN default_messages msg ON (msgkey.id = msg.id)
    -> LEFT JOIN
    -> (
    ->     SELECT * FROM default_recipient
    ->     WHERE user_id = 2 AND deleted = 0
    -> ) rcp ON (msgkey.id = rcp.message_id)
    -> \G
*************************** 1. row ***************************
            message_id: NULL
               user_id: NULL
                  read: NULL
   recipient_read_date: NULL
               deleted: NULL
                    id: 2
               subject: sadasdasd
               message: asdasd
        sender_user_id: 2
   reply_to_message_id: 0
thread_root_message_id: 0
                  date: 2012-05-11 09:44:23
               deleted: 0
            message_id: 2
1 row in set (0.01 sec)

mysql>

I altered the query to test the join of the two tables:

mysql> SELECT
    ->     rcp.*, msg.*, msg.id as message_id
    -> FROM
    -> (
    ->     SELECT id FROM default_messages
    ->     WHERE sender_user_id = 2 AND deleted = 0
    ->     ORDER BY date DESC
    -> ) msgkey
    -> LEFT JOIN default_messages msg ON (msgkey.id = msg.id)
    -> LEFT JOIN
    -> (
    ->     SELECT * FROM default_recipient
    ->     WHERE user_id = 2 AND deleted = 0
    -> ) rcp ON (msgkey.id = rcp.message_id)
    -> \G
*************************** 1. row ***************************
            message_id: NULL
               user_id: NULL
                  read: NULL
   recipient_read_date: NULL
               deleted: NULL
                    id: 2
               subject: sadasdasd
               message: asdasd
        sender_user_id: 2
   reply_to_message_id: 0
thread_root_message_id: 0
                  date: 2012-05-11 09:44:23
               deleted: 0
            message_id: 2
1 row in set (0.01 sec)

mysql>

I get one row. As the two tables grow, the indexes will help the refactored query run fast.

Give it a Try !!!

share|improve this answer
add comment

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
share|improve this answer
    
that one doesn't return anything –  ReynierPM May 15 '12 at 13:37
1  
@ReynierPM Then you need to give us some sample data and expected output. We are guessing here. You probably have a dupe on one of your join keys but its impossible to tell without more data. –  JNK May 15 '12 at 13:37
    
Results are here pastebin.com/Vd6zS9d3 –  ReynierPM May 15 '12 at 13:39
    
@ReynierPM put that in your QUESTION. And are those actual or expected? Give us sample data as well plz. –  JNK May 15 '12 at 13:40
    
@ReynierPM: I will delete this answer. Please do as JNK advised. Post your results and what is your desired result in the question (edit it). It woudl be good if you described what you want your query to return (say: "all deleted messages of user with id=2 and all its recipients") –  ypercube May 15 '12 at 13:44
show 2 more comments

OK, based on your pasted data, you have two rows that match your criteria. Msg_Id values are 2 and 3.

Your WHERE filter is very loose because of the issues that ypercube pointed out.

Currently you will match ANY rows where msg.sender_id = 2.

You get no rows back when you apply a proper filter because both those messages have rcp.deleted = 1 set.

So, you need to clarify what you want exactly.

Do you want to see non-deleted messages? If so, there are no matches.

Do you want to see messages from user 2? If so, this is the the right result.

Do you want to only filter by msg.deleted=0? If so, remove the rcp.deleted filter and I think you will get what you want if you properly group your filters.

share|improve this answer
    
clarify at post edit –  ReynierPM May 15 '12 at 13:53
    
@ReynierPM Ypercube's answer is correct. You are getting 0 rows because they are both deleted in one of the tables. –  JNK May 15 '12 at 13:55
    
I want to filter by msg.deleted = 0 and rcp.deleted = 0 and msg.sender_user_id = X and rcp.user_id = X –  ReynierPM May 15 '12 at 13:56
    
@ReynierPM yes. If you look at your data all messages for user 2 are deleted in the recipient table. –  JNK May 15 '12 at 13:56
    
hmmm deleted for me is when value takes 1 and not you're wrong see first row at table recipient where deleted = 0 and user_id = 2 –  ReynierPM May 15 '12 at 13:58
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.