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
Best Answer
Are you looking for this?
Sample output:
Here is SQLFiddle demo