Get profiles values where message.sender_user_id = profile.user_id

Question

I have this tables:

default_messages

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;

default_profiles:

CREATE TABLE IF NOT EXISTS `default_profiles` (
 `id` int(9) NOT NULL AUTO_INCREMENT,
 `created` datetime DEFAULT NULL,
 `updated` datetime DEFAULT NULL,
 `created_by` int(11) DEFAULT NULL,
 `ordering_count` int(11) DEFAULT NULL,
 `user_id` int(11) unsigned NOT NULL,
 `display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `company` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 `lang` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
 `bio` text COLLATE utf8_unicode_ci,
 `dob` int(11) DEFAULT NULL,
 `gender` set('m','f','') COLLATE utf8_unicode_ci DEFAULT NULL,
 `phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `mobile` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address_line1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address_line2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address_line3` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `postcode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `website` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `updated_on` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

and

default_recipient:

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;

Then I have this query for get some messages:

SELECT DISTINCT p.display_name, p.first_name, p.last_name, rcp.*, msg.* 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 rcp.user_id = 1 AND rcp.deleted = 0 ORDER BY msg.date DESC

I need to get the values from default_profile where default_profile.user_id = default_messages.sender_user_id but for some reason I get the wrong values. It's something wrong in my query?

Here are some values for testing purpose and also the complete script for table creation: http://pastebin.com/T3mXfi0k

Answer 1

First, what is "wrong" with the data you are returning?

My guess is that you intend to be using an [INNER] JOIN which will only return rows where there is a matching record, not a LEFT JOIN which will return all the rows in the left table regardless of whether there are matches in the right table.

For example:

mysql> select distinct p.display_name, p.first_name, p.last_name, m.sender_user_id 
    -> FROM default_profiles p 
    -> JOIN default_messages m ON m.sender_user_id = p.user_id;
+--------------------+------------+------------+----------------+
| display_name       | first_name | last_name  | sender_user_id |
+--------------------+------------+------------+----------------+
| Reynier Perez Mira | Reynier    | Perez Mira |              1 |
| User1              | User1      | Lastname1  |              2 |
+--------------------+------------+------------+----------------+
2 rows in set (0.00 sec)

vs

mysql> select distinct p.display_name, p.first_name, p.last_name, m.sender_user_id 
    -> FROM default_profiles p
    -> LEFT JOIN default_messages m ON m.sender_user_id = p.user_id;
+--------------------+------------+---------------+----------------+
| display_name       | first_name | last_name     | sender_user_id |
+--------------------+------------+---------------+----------------+
| Reynier Perez Mira | Reynier    | Perez Mira    |              1 |
| User1              | User1      | Lastname1     |              2 |
| Tomas Losis        | Tomas      | Losis         |           NULL |
| Roberto            | Roberto    | Scharffenorth |           NULL |
| webmaster 3wd      | webmaster  | 3wd           |           NULL |
| polar              | polar      | pilsen        |           NULL |
| rubi               | rubi       | acosta        |           NULL |
+--------------------+------------+---------------+----------------+
7 rows in set (0.00 sec)

I would rewrite your provided query like so:

SELECT DISTINCT p.display_name, p.first_name, p.last_name, rcp.*, msg.*
FROM default_profiles p 
JOIN default_messages msg ON p.user_id = msg.sender_user_id 
JOIN default_recipient rcp ON msg.id = rcp.message_id
WHERE rcp.user_id = 1 
  AND rcp.deleted = 0
ORDER BY msg.date DESC;

That being said, in your sample data, both our queries return the same results. Perhaps there is different data in the full set.