SELECT A.*
FROM default_relations_users A
LEFT JOIN default_relations_users B
ON A.id_user_rq = B.id_user_ap
AND A.id_user_ap = B.id_user_rq
WHERE B.id_user_rq IS NULL;
I loaded you sample data (I added an additional index)
mysql> use Reynierpm
Database changed
mysql> DROP TABLE IF EXISTS `default_relations_users`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `default_relations_users` (
-> `id_user_rq` int(11) NOT NULL,
-> `id_user_ap` int(11) NOT NULL,
-> UNIQUE KEY `rusers_rq_ap_idx` (`id_user_rq`,`id_user_ap`),
-> UNIQUE KEY `rusers_ap_eq_idx` (`id_user_ap`,`id_user_rq`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO `default_relations_users` (`id_user_rq`, `id_user_ap`) VALUES
-> (1, 2), (1, 3), (1, 4), (2, 1), (2, 2), (2, 3);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from default_relations_users;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
+------------+------------+
6 rows in set (0.00 sec)
mysql>
Here is the result of my answer
mysql> SELECT A.*
-> FROM default_relations_users A
-> LEFT JOIN default_relations_users B
-> ON A.id_user_rq = B.id_user_ap
-> AND A.id_user_ap = B.id_user_rq
-> WHERE B.id_user_rq IS NULL;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
| 1 | 3 |
| 1 | 4 |
| 2 | 3 |
+------------+------------+
3 rows in set (0.00 sec)
mysql>
Give it a Try !!!
mysql> SELECT A.*
-> FROM default_relations_users A
-> LEFT JOIN default_relations_users B
-> ON A.id_user_rq = B.id_user_ap
-> AND A.id_user_ap = B.id_user_rq
-> WHERE B.id_user_rq IS NULL
-> AND A.id_user_rq = 1;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
| 1 | 3 |
| 1 | 4 |
+------------+------------+
2 rows in set (0.01 sec)
mysql>
Ok, first off I think you are almost there. From looking at the schema and data one thing I have noticed is that you seem to have overlooked the fact that a friendship is bi-directional. So when you create a friend entry from a request you also need to create one in the other direction as well:
INSERT INTO `default_friend` (`friend_id`, `user_id`, `is_suscriber`, `privacy`, `created_at`, `friend_list_id`, `approved`)
VALUES (1, 2, 1, 0, '2012-08-13 18:16:11', 0, 1);
After you have done that your query should be more like the result you are after. Running this query:
select distinct u.id as `user_id`, u.username, f.id as `friend_id`, f.username as friend, s.*
from default_users as u
left join default_friend as df on df.user_id = u.id
left join default_users as f on f.id = df.friend_id
left join default_status as s on s.user_id = u.id
left join default_comment as c on c.status_id = s.status_id
order by s.status_id;
returns the following result set:
user_id username friend_id friend status_id message created_at privacy user_id is_reply device
1 admin 2 demo 1 dasdasdasdasdasd 2012-08-13 19:45:37 NULL 1 0
2 demo 1 admin 2 dasdasdasdasdasd 2012-08-13 19:46:03 NULL 2 0
1 admin 2 demo 3 dasdsad344hbvnbnhjhgjhjghjhj 2012-08-13 21:54:53 NULL 1 0
Is this anywhere near what you are looking for?
Best Answer
I think I found the solution for this case. My solution is to use
SUM()
aggregation withIF()
condition. The final logic is substitute COUNT(*) to boolean - have rows or empty query. This query will return results only if it match both conditions:SELECT SUM(IF((value='john' AND type='first_name') OR (value='doe' AND type='last_name'), 1, 0)) AS cnt FROM tablename HAVING cnt=2;
If I want to select all data where first name is
John
and last name isDoe
OR last name isSmith
, query will be:SELECT sum(IF((value='john' AND type='first_name') OR (value='doe' AND type='last_name'), 1, 0)) AS first_last, SUM(IF(value='smith' AND type='last_name', 1, 0)) AS last FROM tablename HAVING first_last=2 OR last=1;