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'd like to integrate the following query:

SELECT
    parent.id
  , COUNT(child.id) AS child_count
FROM
             messages parent
  INNER JOIN messages child
    ON child.parent_id = parent.id
WHERE parent.parent_id = 0
GROUP BY parent.id;

into this query:

SELECT m.id,m.seen, m.uid, m.hash, m.date_created,m.subject,
m.textplain,m.texthtml, CONCAT_WS('@', a.localpart, a.domain ) 
AS address 
FROM messages m 
LEFT JOIN message_address_fields maf ON maf.message_id = m.id 
LEFT JOIN addresses a ON a.id = maf.address_id 
WHERE maf.field_id =4 AND m.user_id =1

Here's a sql fiddle: http://sqlfiddle.com/#!2/bef27/1

Here's my schema:

 CREATE TABLE IF NOT EXISTS `addresses` (
   `id` int(10) NOT NULL AUTO_INCREMENT,
   `user_id` int(10) NOT NULL,
   `name` text NOT NULL,
   `localpart` text NOT NULL,
   `domain` text NOT NULL,
   PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `fields` (
   `id` int(10) NOT NULL AUTO_INCREMENT,
   `name` text,
  PRIMARY KEY (`id`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `account_folder_id` int(10) NOT NULL,
  `hash` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `texthtml` text NOT NULL,
  `textplain` text NOT NULL,
  `uid` int(10) NOT NULL,
  `seen` tinyint(1) NOT NULL,
  `flagged` tinyint(1) NOT NULL,
  `date_created` int(11) NOT NULL DEFAULT '0',
  `last_modified` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash` (`hash`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `message_address_fields` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `message_id` int(10) NOT NULL,
  `field_id` int(10) NOT NULL,
  `address_id` int(10) NOT NULL,
  `date_created` int(11) NOT NULL DEFAULT '0',
  `last_modified` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Thanks for the help!

QUESTION: This query limits the data to only messages that contain an address with field id of 4. How would I make it optional to have a correlated address with a field id of 4 or not

share|improve this question
1  
Please show us the query you're using. I promise we're not here to steal your code or your business. –  jcolebrand Jul 14 '13 at 3:55
    
Also helpful is creating a basic example sqlfiddle.com example since you're using mysql. –  jcolebrand Jul 14 '13 at 3:56
    
Updated my question. Going to create a fiddle now –  jkushner Jul 15 '13 at 20:18
    
Fiddle: sqlfiddle.com/#!2/7ddb4 –  jkushner Jul 15 '13 at 20:32
add comment

2 Answers

up vote 0 down vote accepted

Are you looking for this?

SELECT m.id,m.seen, m.uid, m.hash, m.date_created,m.subject,
       m.textplain,m.texthtml, CONCAT_WS('@', a.localpart, a.domain) address,
       (
         SELECT COUNT(*) 
           FROM messages parent JOIN messages child
             ON child.parent_id = parent.id
          WHERE parent.id = m.id
       ) child_count
  FROM messages m LEFT JOIN message_address_fields maf 
    ON maf.message_id = m.id LEFT JOIN addresses a 
    ON a.id = maf.address_id
 WHERE maf.field_id = 4 
   AND m.user_id = 1 
 LIMIT 30

Sample output:

| ID | SEEN | UID | ... | CHILD_COUNT |
------------------- ... ---------------
|  1 |    0 |   2 | ... |           2 |
|  2 |    0 |   3 | ... |           0 |
|  3 |    0 |   4 | ... |           0 |
|  4 |    0 |   5 | ... |           0 |

Here is SQLFiddle demo

share|improve this answer
add comment
SELECT m.id,m.seen, m.uid, m.hash, m.date_created,m.subject,
m.textplain,m.texthtml, CONCAT_WS('@', a.localpart, a.domain ) 
AS address,COUNT(child.id) AS child_count 
FROM messages m 
INNER JOIN messages child ON child.parent_id = m.id
LEFT JOIN message_address_fields maf ON maf.message_id = m.id 
LEFT JOIN addresses a ON a.id = maf.address_id 
WHERE maf.field_id =4 AND m.user_id =1 LIMIT 0 , 30
share|improve this answer
    
this does not work because if there are no children than it doesnt show the row –  jkushner Jul 15 '13 at 23:21
add comment

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.