I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id but didn't know how to compare the users.user_id with friend.friend_id and friend.user_id in the same query. I could do that at development level but I don't want that because when I add LIMIT could be a horror iterate between results. Any help?
--
-- Table structure for table `default_comment`
--
CREATE TABLE IF NOT EXISTS `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 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
--
-- Table structure for table `default_friend`
--
CREATE TABLE IF NOT EXISTS `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;
INSERT INTO `default_friend` (`friend_id`, `user_id`, `is_suscriber`, `privacy`, `created_at`, `friend_list_id`, `approved`)
VALUES
(0, 1, 1, 0, '2012-08-13 18:15:54', 0, 1),
(2, 1, 1, 0, '2012-08-13 18:16:11', 0, 1);
--
-- Table structure for table `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,
`updated_on` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;
INSERT INTO `default_profiles` (`id`, `created`, `updated`, `created_by`, `ordering_count`, `user_id`, `display_name`, `first_name`, `last_name`, `updated_on`)
VALUES
(1, NULL, NULL, NULL, NULL, 1, 'Reynier Perez Mira', 'Reynier', 'Perez Mira', 1344889677),
(2, '2012-08-13 11:44:29', NULL, 1, 1, 2, 'Demo', 'Demo', 'Demo', NULL);
--
-- Table structure for table `default_status`
--
CREATE TABLE IF NOT EXISTS `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 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
--
-- Table structure for table `default_users`
--
CREATE TABLE IF NOT EXISTS `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 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='' AUTO_INCREMENT=3 ;
INSERT INTO `default_users` (`id`, `email`, `password`, `salt`, `group_id`, `ip_address`, `active`, `activation_code`, `created_on`, `last_login`, `username`, `forgotten_password_code`, `remember_code`)
VALUES
(1, '[email protected]', '87cdcd2c46479ef677689526cc1a71adebfdcde3', '07f7d', 1, '', 1, '', 1344872988, 1344893013, 'admin', NULL, NULL),
(2, '[email protected]', '2ff62db826f753e0f7712578eddcea778a452448', '05e9a0', 7, '127.0.0.1', 1, NULL, 1344890669, 1344891197, 'demo', NULL, NULL);
I build this one:
SELECT DISTINCT s.*, c.*, p.*, u.*,
(SELECT f.friend_id FROM default_friend f WHERE (f.user_id = u.id AND f.friend_id = 1 ) OR (f.friend_id=u.id AND f.user_id=1) LIMIT 1)) AS f_friend_id,
(SELECT f.user_id FROM default_friend f WHERE (f.user_id = u.id AND f.friend_id = 1 ) OR (f.friend_id=u.id AND f.user_id=1) LIMIT 1)) AS f_user_id,
FROM profiles p, users u, status s
LEFT JOIN comment c ON (c.status_id = s.status_id)
WHERE s.user_id = 1
but I'm not so sure if it works and if it's optimized because I will have a lot of rows on those tables. Any advice or help or tip?