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've several days dealing with this query but can't get it to works as I want. This post is related to this one Several values from different tables in which I tough found the solution but I was totally wrong and lost :(. So basically is the same schema as the other post: 5 tables: default_users, default_profiles, default_status, default_comment and default_friend. This are the SQL for each:

default_comment

CREATE TABLE `default_comment` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `friend_id` int(11) NOT NULL,
  `message` text COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `status_id` int(11) NOT NULL,
  `device` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`comment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13007 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

default_friend

CREATE TABLE `default_friend` (
  `friend_id` int(8) NOT NULL,
  `user_id` int(8) NOT NULL,
  `is_suscriber` tinyint(1) NOT NULL DEFAULT '1',
  `privacy` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `friend_list_id` int(4) NOT NULL,
  `approved` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

default_profiles

CREATE TABLE `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,
  `updated_on` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

default_status

CREATE TABLE `default_status` (
  `status_id` int(11) NOT NULL AUTO_INCREMENT,
  `message` text COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `privacy` tinyint(1) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `is_reply` tinyint(1) NOT NULL DEFAULT '0',
  `device` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`status_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13005 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

default_users

CREATE TABLE `default_users` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(60) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `password` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `salt` varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `group_id` int(11) DEFAULT NULL,
  `ip_address` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` int(1) DEFAULT NULL,
  `activation_code` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_on` int(11) NOT NULL,
  `last_login` int(11) NOT NULL,
  `username` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `forgotten_password_code` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `remember_code` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Registered User Information'

So I build this query

SELECT DISTINCT
    u.id AS user_id,
    u.email,
    p.display_name,
    p.first_name,
    p.last_name,
    s.status_id,
    s.message,
    f.friend_id,
    f.user_id,
    UNIX_TIMESTAMP(s.created_at) AS created_at
FROM
    default_users u
        LEFT JOIN
    default_friend f ON ((f.friend_id = 1 OR f.user_id = 1) AND f.approved = 1)
        LEFT JOIN
    default_status s ON (u.id = s.user_id OR f.friend_id = s.user_id OR f.user_id = s.user_id)
        LEFT JOIN
    default_profiles p ON (s.user_id = p.user_id)
WHERE
    u.id = 1
ORDER BY s.created_at DESC
LIMIT 0 , 10

And this is the result:

+---------+---------------------+--------------------+-----------------------------+----------------------------+-----------+------------------------------------------------------------------------------------------------------+-----------+---------+------------+
| user_id | email               | display_name       | first_name                  | last_name                  | status_id | message                                                                                              | friend_id | user_id | created_at |
+---------+---------------------+--------------------+-----------------------------+----------------------------+-----------+------------------------------------------------------------------------------------------------------+-----------+---------+------------+
|       1 | reynierpm@gmail.com | Reynier Perez Mira | Reynier                     | Perez Mira                 |     12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w |         2 |       1 | 1345055203 |
|       1 | reynierpm@gmail.com | Reynier Perez Mira | Reynier                     | Perez Mira                 |     12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w |         1 |       3 | 1345055203 |
|       1 | reynierpm@gmail.com | Reynier Perez Mira | Reynier                     | Perez Mira                 |     12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w |         1 |    4315 | 1345055203 |
|       1 | reynierpm@gmail.com | Reynier Perez Mira | Reynier                     | Perez Mira                 |     12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w |      6380 |       1 | 1345055203 |
|       1 | reynierpm@gmail.com | Demo               | Demo                        | Demo                       |     10484 | ivp1e40f350bf6ifplzsd560k6e5mvgvdq91q7j2k5oldraoftbd2k2eirtdihh7fbqwq2mkohqi05d3fw0sawvqmabah979updo |         2 |       1 | 1345055064 |
|       1 | reynierpm@gmail.com | User 1             | User                        | User                       |     10494 | arstvbkjrea725bea4mv1kv1qaelt669n7mequh5j6n5uzh8a3voy7w6lvebphrhkhnks9fzos46p8sqptud1mb3wysjpge653qe |         1 |       3 | 1345055064 |
|       1 | reynierpm@gmail.com | user502bc8a4942d1  | user502bc8a4942d1-firstname | user502bc8a4942d1-lastname |      7796 | vmkhv266hu8s7czpy1j2m1cvi3j1i3jr35hl76fsdqdv1cacv5etylls3q6yqrm7r2nrymjcqrvtzu267huajs5g9j9t3kdz6f9l |         1 |    4315 | 1345054882 |
|       1 | reynierpm@gmail.com | user502bc90a00ba4  | user502bc90a00ba4-firstname | user502bc90a00ba4-lastname |      6103 | c3mmq89ucbcjd0w1pdak0tgtioyfy5loa3z18ecmkwukusojbr6qwosbham8d132fzq1pn6ei3h5dgoabyvytyyuiqm2k09fmudy |      6380 |       1 | 1345054774 |
|       1 | reynierpm@gmail.com | Demo               | Demo                        | Demo                       |      3828 | hky500btaj5rq96inod22k1zpyh1uz65892mwanuloqkqgjo1h3u2f0ikery3pe3olwoqbqq08t301vqpuhirvddp3lq6w5ppoep |         2 |       1 | 1345054631 |
|       1 | reynierpm@gmail.com | Reynier Perez Mira | Reynier                     | Perez Mira                 |      2364 | zfe25at726i4bsju21v3ipcpp6bhetdrqva7m4ceh0k2pcdj6equbnq89mlg892eh5eqq1l1514qir76pqq5u7qnqyai6dqnv45n |         2 |       1 | 1345054536 |
+---------+---------------------+--------------------+-----------------------------+----------------------------+-----------+------------------------------------------------------------------------------------------------------+-----------+---------+------------+
10 rows in set (0.08 sec)

But if I run this query to get my friends:

SELECT 
    *
FROM
    default_friend
WHERE
    user_id = 1 OR friend_id = 1

This is the result

+-----------+---------+--------------+---------+---------------------+----------------+----------+
| friend_id | user_id | is_suscriber | privacy | created_at          | friend_list_id | approved |
+-----------+---------+--------------+---------+---------------------+----------------+----------+
|         2 |       1 |            1 |       0 | 2012-08-13 13:46:11 |              0 |        1 |
|         1 |       3 |            1 |       0 | 2012-08-14 14:46:11 |              0 |        1 |
|         1 |    4315 |            1 |       0 | 2012-08-15 11:57:51 |              0 |        1 |
|      6380 |       1 |            1 |       0 | 2012-08-15 11:58:47 |              0 |        1 |
+-----------+---------+--------------+---------+---------------------+----------------+----------+
4 rows in set (0.01 sec)

Also if I run this other to get messages from me and all my friends:

SELECT 
    *
FROM
    default_status
WHERE
    user_id = 1 OR user_id = 3 OR user_id = 4315 OR user_id = 6380

I get this results:

+-----------+------------------------------------------------------------------------------------------------------+---------------------+---------+---------+----------+--------+
| status_id | message                                                                                              | created_at          | privacy | user_id | is_reply | device |
+-----------+------------------------------------------------------------------------------------------------------+---------------------+---------+---------+----------+--------+
|         1 | dasdasdasdasdasd                                                                                     | 2012-08-13 15:15:37 |    NULL |       1 |        0 |        |
|         3 | dasdsad344hbvnbnhjhgjhjghjhj                                                                         | 2012-08-13 17:24:53 |    NULL |       1 |        0 |        |
|         4 | dasdsad344hbvnbnhjhgjhjghjhjsdfsdfsdfsdfdsfsdfsdfsdfsdfsdfsdfsdfsdfsdfsdfsdf                         | 2012-08-13 17:24:53 |    NULL |       3 |        0 |        |
|       834 | pgmafjmzicgdqu1fo0fv3sg4qipq4dm3qnubpo4gpyfz5y7q0p9kl75c18c9tsr01u92fqfoyyw12vl7zn1bht4vhnyf2b5cldrp | 2012-08-15 13:43:57 |    NULL |       3 |        0 |        |
|      2364 | zfe25at726i4bsju21v3ipcpp6bhetdrqva7m4ceh0k2pcdj6equbnq89mlg892eh5eqq1l1514qir76pqq5u7qnqyai6dqnv45n | 2012-08-15 13:45:36 |    NULL |       1 |        0 |        |
|      6103 | c3mmq89ucbcjd0w1pdak0tgtioyfy5loa3z18ecmkwukusojbr6qwosbham8d132fzq1pn6ei3h5dgoabyvytyyuiqm2k09fmudy | 2012-08-15 13:49:34 |    NULL |    6380 |        0 |        |
|      7796 | vmkhv266hu8s7czpy1j2m1cvi3j1i3jr35hl76fsdqdv1cacv5etylls3q6yqrm7r2nrymjcqrvtzu267huajs5g9j9t3kdz6f9l | 2012-08-15 13:51:22 |    NULL |    4315 |        0 |        |
|     10494 | arstvbkjrea725bea4mv1kv1qaelt669n7mequh5j6n5uzh8a3voy7w6lvebphrhkhnks9fzos46p8sqptud1mb3wysjpge653qe | 2012-08-15 13:54:24 |    NULL |       3 |        0 |        |
|     12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w | 2012-08-15 13:56:43 |    NULL |       1 |        0 |        |
+-----------+------------------------------------------------------------------------------------------------------+---------------------+---------+---------+----------+--------+
9 rows in set (0.02 sec)

So after this explanation what's wrong in my first query? What I get results in other select that aren't present in the first query? (the one with JOIN), any help or advice? Very important the file for testing is here at my Dropbox https://www.dropbox.com/s/tu4rb7osppyd8un/comvivem_db.sql

share|improve this question
    
Back again? I'm just on the way back from work. Once I'm home let's chat and fix this before you have to raise a fourth question :-) –  Mr.Brownstone Aug 16 '12 at 15:36
    
@mr-brownstone thanks but I really need help on this, as you notice I'm doing my home tasks but can't get this to work :( where are U from? Or better which time is there now and what time you'll be in your home –  ReynierPM Aug 16 '12 at 15:41
    
Will be back home in about 30 minutes, it is 16:45 in the UK. When is your deadline? –  Mr.Brownstone Aug 16 '12 at 15:43
    
@Mr.Brownstone Ohhh I'm in Caracas, Venezuela and here is 11:18 AM so it's working time yet :) but don't worry about that because this is part of my job the dawn query !!! Sorry for that comment! –  ReynierPM Aug 16 '12 at 15:47
    
Ok, let's make sure we solve it once and for all today. –  Mr.Brownstone Aug 16 '12 at 15:50

1 Answer 1

up vote 5 down vote accepted

Ok, so based on our conversation if you make sure that when a friend's request is created you also create an entry in the other direction: (i.e. Friend_A asks Friend_B, but you also need a relationship between Friend_B and Friend_A) then you can run the following query to obtain the results that you need:

select distinct
s.user_id, 
u.username,
p.display_name,
p.first_name,
p.last_name,
s.message
from default_status as s
join default_friend as f on f.user_id = s.user_id
join default_users as u on u.id = s.user_id
join default_profiles as p on p.user_id = u.id
where (f.user_id = 1 or f.friend_id = 1)
and f.approved = 1
order by s.created_at desc;

This will also make it easier to obtain a list of all of a persons friend's:

select *
from default_users as u
join default_friends as f on f.friend_id = u.id
where f.user_id = 1;

Just remember to put an index over user_id and friend_id in the default friends_table otherwise you will find your query may take a while:

create unique index idx_friend on default_friend(user_id,friend_id);

I hope this helps you. ;-)

share|improve this answer
    
@mr-brownstone still having problems :( ping!! –  ReynierPM Aug 16 '12 at 20:43
    
@mr-browstone ok, for some reason and also changing from my approach (which use BuddyPress by the way) to yours I still getting duplicates on the main query, the big one, also I notice that trying to COUNT the results for use as pagination parameter I get 25 records instead of 12 and doesn't know if I can use DISTINCT in a COUNT(*) sentence, this are the issues –  ReynierPM Aug 17 '12 at 2:16

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.