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 this tables:

--
-- Table structure for table `default_cmvblog`
--

CREATE TABLE IF NOT EXISTS `default_cmvblog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `category_id` int(11) NOT NULL,
  `intro` text COLLATE utf8_unicode_ci NOT NULL,
  `body` text COLLATE utf8_unicode_ci NOT NULL,
  `keywords` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `author_id` int(11) NOT NULL DEFAULT '0',
  `created_on` date DEFAULT NULL,
  `updated_on` date DEFAULT NULL,
  `comments_enabled` int(1) NOT NULL DEFAULT '1',
  `status` enum('draft','live') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'draft',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_title` (`title`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=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,
  `phone_1` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone_2` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `avatar` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=23172 ;

--
-- Dumping data for table `default_profiles`
--

INSERT INTO `default_profiles` (`id`, `created`, `updated`, `created_by`, `ordering_count`, `user_id`, `display_name`, `first_name`, `last_name`, `updated_on`, `phone_1`, `phone_2`, `avatar`) VALUES
(1, NULL, NULL, NULL, NULL, 1, 'ReynierPM', 'Reynier', 'Perez Mira', 1346098402, '04241805609', '04241805609', 1),
(2, '2012-08-13 11:44:29', NULL, 1, 1, 2, 'Demo', 'Demo', 'Demo', NULL, NULL, NULL, NULL),
(3, '2012-08-27 10:09:35', NULL, 1, 2, 3, '86a7d120c', '86a7d120c-FN', '86a7d120c-LN', NULL, NULL, NULL, NULL),
(4, '2012-08-27 10:09:35', NULL, 1, 3, 4, '86a7d48a6', '86a7d48a6-FN', '86a7d48a6-LN', NULL, NULL, NULL, NULL),
(5, '2012-08-27 10:09:35', NULL, 1, 4, 5, '86a7d5989', '86a7d5989-FN', '86a7d5989-LN', NULL, NULL, NULL, NULL),
(6, '2012-08-27 10:09:35', NULL, 1, 5, 6, '86a7d6a45', '86a7d6a45-FN', '86a7d6a45-LN', NULL, NULL, NULL, NULL),
(7, '2012-08-27 10:09:35', NULL, 1, 6, 7, '86a7d7bcd', '86a7d7bcd-FN', '86a7d7bcd-LN', NULL, NULL, NULL, NULL),
(8, '2012-08-27 10:09:35', NULL, 1, 7, 8, '86a7d8db6', '86a7d8db6-FN', '86a7d8db6-LN', NULL, NULL, NULL, NULL),
(9, '2012-08-27 10:09:35', NULL, 1, 8, 9, '86a7d9eb9', '86a7d9eb9-FN', '86a7d9eb9-LN', NULL, NULL, NULL, NULL),
(10, '2012-08-27 10:09:35', NULL, 1, 9, 10, '86a7daf8c', '86a7daf8c-FN', '86a7daf8c-LN', NULL, NULL, NULL, NULL),
(11, '2012-08-27 10:09:35', NULL, 1, 10, 11, '86a7dc0db', '86a7dc0db-FN', '86a7dc0db-LN', NULL, NULL, NULL, NULL),
(12, '2012-08-27 10:09:35', NULL, 1, 11, 12, '86a7dd239', '86a7dd239-FN', '86a7dd239-LN', NULL, NULL, NULL, NULL),
(13, '2012-08-27 10:09:35', NULL, 1, 12, 13, '86a7de330', '86a7de330-FN', '86a7de330-LN', NULL, NULL, NULL, NULL);

--
-- 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=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Registered User Information' AUTO_INCREMENT=23172 ;

--
-- Dumping data for table `default_users`
--

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, 1346114876, 'admin', NULL, NULL),
(2, 'demo@demo.com', '2ff62db826f753e0f7712578eddcea778a452448', '05e9a0', 7, '127.0.0.1', 1, NULL, 1344890669, 1345251732, 'demo', NULL, NULL),
(3, '86a7d120c@local.com', '8f1e0454cb0e88a3a6ae5657b66c092bb5300e01', '7a2758', 4, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d120c', NULL, NULL),
(4, '86a7d48a6@local.com', '9e760c1f1f1525a035b3947df469c64d2be20843', '8da707', 5, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d48a6', NULL, NULL),
(5, '86a7d5989@local.com', '515ffc6a6c8996ced8699587b7cd192abb361482', '7b01ef', 3, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d5989', NULL, NULL),
(6, '86a7d6a45@local.com', '013bab81603455638e6f2ed6d5f95ac4a57deba7', '5f71d2', 5, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d6a45', NULL, NULL),
(7, '86a7d7bcd@local.com', '56ce2274480ce0a853d33e1ca026f75b4859a281', '9d4234', 2, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d7bcd', NULL, NULL),
(8, '86a7d8db6@local.com', 'f206a3085bf9280adaf7841686c0193edad1194f', '00d026', 3, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d8db6', NULL, NULL),
(9, '86a7d9eb9@local.com', '8ffc88beea7473a7e2dac8493749b4ed1bfe27d1', '66d998', 6, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d9eb9', NULL, NULL),
(10, '86a7daf8c@local.com', '8ff2ab65ced1752e1fc9a5856721538ee524df31', '312260', 6, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7daf8c', NULL, NULL),
(11, '86a7dc0db@local.com', '13ad8228b71355bc21a2876ba6d684d9826a5aff', 'b3cffb', 5, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7dc0db', NULL, NULL),
(12, '86a7dd239@local.com', '5ba3d3e5cfed7bb9436fc97f5d0efa033742043a', '6bdd6e', 6, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7dd239', NULL, NULL),
(13, '86a7de330@local.com', '5b713fc9883923a75c565ea92abf612862c306c6', 'c4ae49', 3, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7de330', NULL, NULL);

So I made this query:

    SELECT 
    COUNT(*) AS num_rows
FROM
    default_cmvblog b
        JOIN
    default_users u ON (b.author_id = u.id)
WHERE
    ((b.title LIKE '%86a7%'
        OR b.body LIKE '%86a7%'
        OR b.keywords LIKE '%86a7%')
        AND b.status = 'live')
        OR (u.email LIKE '%86a7%'
        OR u.username LIKE '%86a7%' AND u.id != 1
        AND u.active = 1)

This should return any record from default_cmvblog where title, content or keywords is like 86a7 and also status is live, right? The same for default_users should return records where email or username like 86a7 and id != 1 and active = 1, right? But I get 0 as the COUNT why? What's wrong in my query?

Cheers and thanks in advance

share|improve this question
    
    
Well, given what you've given us, there's no data in the cmvblog table –  swasheck Aug 27 '12 at 21:02
    
@swasheck yes but there is on users table look for "%86a7%" and those results should be counted to. This is what I'm trying to do –  ReynierPM Aug 27 '12 at 21:06

1 Answer 1

up vote 1 down vote accepted

And binds tighter than Or. I think you want your query here:

OR (u.email LIKE '%86a7%'
        OR u.username LIKE '%86a7%' AND u.id != 1
        AND u.active = 1)

Needs to have one more set of parenthesis. Also, you need to change your join into an outer join to count records in both tables. You also need to remember that the join will be null if it doesn't exist in both. So you need to pick a non-null column to count the record in the table in which it does exist and check if it's null.

FROM
default_cmvblog b
    LEFT OUTER JOIN
default_users u ON (b.author_id = u.id)
...
OR ((u.email LIKE '%86a7%'
        OR u.username LIKE '%86a7%') AND (u.id != 1 OR u.id IS NULL)
        AND u.active = 1)

You also need to

What you want is a full join if it should exist in either. A left join will give you the left table, but the right only if it exists in the left as well. So, if you are looking for the right along with the left, you will need to create a view or do a correlated subquery with a sum to get the results you want since there will be two rows in the destination table no matter what.

See here for full outer joins: http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html

See here for correlated subqueries (basically a select statement you can join to like a table): http://stackoverflow.com/questions/530381/mysql-correlated-subquery-in-join-syntax

share|improve this answer
    
@wes-stueve Thx for your answer it was helpful but I didn't use your solution. I use UNION which return results in two columns but then programmatically , subqueries or subselect consume a lot of resources. Now my query for COUNT looks like this SELECT COUNT(*) AS num_rows_cmvblog FROM default_cmvblog b WHERE (b.title LIKE '%86a7%' OR b.body LIKE '%86a7%') AND b.status = 'live' UNION SELECT COUNT(*) AS num_rows_users FROM default_users u WHERE (u.email LIKE '%86a7%' OR u.username LIKE '%86a7%') AND u.id != 1 AND u.active =1 –  ReynierPM Aug 28 '12 at 14:45
    
@wes-stueve Now my question is should the same UNION works for get records from both tables using the same condition? I mean because this query will work for COUNT but I'm not so sure about the SELECT for columns –  ReynierPM Aug 28 '12 at 14:46
    
@ReynierPM It will use the column name from the first select. However, UNION selects only distinct records. I haven't tried it like this, but I suspect that it may return only one record if the values are equal. You probably want to use UNION ALL. Also, you can SELECT SUM(nm_rows) FROM (SELECT COUNT(*) As num_rows ... UNION ALL ...) AS Tbl1 to get the total. Or you can create two views and do the same thing. –  wes.stueve Aug 29 '12 at 9:14
    
@wes-stueve thanks I give a try to those suggestions –  ReynierPM Aug 29 '12 at 13:00

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.