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 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, 'reynierpm@gmail.com', '87cdcd2c46479ef677689526cc1a71adebfdcde3', '07f7d', 1, '', 1, '', 1344872988, 1344893013, 'admin', NULL, NULL),
(2, 'demo@demo.com', '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?

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.