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 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

share|improve this question
    
+1 for providing sample data –  Aaron Brown Jul 12 '12 at 21:57
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
Hi, I was complety wrong because the query works fine, the problem was in my code and I found it. Anyway your suggestions are good too and show me something new today. Cheers –  ReynierPM Jul 13 '12 at 12:30
add comment

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.