Таблицы
| Код |
CREATE TABLE IF NOT EXISTS `user_friends` (
`uid` int(10) unsigned NOT NULL,
`group_id` tinyint(3) unsigned NOT NULL,
`friends_ids` mediumtext COLLATE utf8_unicode_ci,
`group_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`uid`,`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `user_profile`
--
CREATE TABLE IF NOT EXISTS `user_profile` (
`uid` int(10) unsigned NOT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`nickname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
Запрос
| Код |
SELECT *
FROM `way`.`user_friends`
RIGHT JOIN `way`.`user_profile` ON `user_profile`.`uid`
IN (
`user_friends`.`friends_ids`
)
WHERE `user_friends`.`uid` =118
|
Результат
| Код |
uid group_id friends_ids group_name uid email phone nickname
118 1 97,116,117 NULL 97 none none aaa
118 2 116 dfgfdgf 116 [email protected] NULL iadshtf
|
По моим представлениям, тут не хватает ещё 2 строчек, где group_id был бы 1, а uid 116 и 117 |
