Re: LEFT JOIN WHERE GROUP BY

[Daniel Flöijer]

Sorry, create_date belongs to support. I think this should be everything needed:

CREATE TABLE `support` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`statusinternal_id` tinyint(3) unsigned NOT NULL DEFAULT '1',
`create_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9591 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 11264 kB; InnoDB free: 11264 kB; InnoDB free: 1'

CREATE TABLE `support_internalstatus` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1

insert into support_internalstatus values
( 1, 'Warranty' ), ( 2, 'Wrong item delivered' ),
(3, 'Exchanged'), ( 4, 'Warranty expired' )

insert into support values
( 1, '2011-01-01 00:00:01' ), ( 1, '2011-03-01 00:00:01' ),
( 2, '2011-01-01 00:00:01' ), ( 2, '2011-02-01 00:00:01' ),
( 2, '2011-02-01 00:00:01' ), ( 2, '2011-04-01 00:00:01' ),
( 3, '2011-01-01 00:00:01' ), ( 3, '2011-02-01 00:00:01' ),
( 3, '2011-02-01 00:00:01' ), ( 3, '2011-02-01 00:00:01' ),
( 4, '2011-02-01 00:00:01' ), ( 4, '2011-02-01 00:00:01' )

I then get something like this with my query:

2, 'Wrong item delivered'
3, 'Exchanged'
2, 'Warranty expired'

What I want is this:

0 (or null), 'Warranty'
2, 'Wrong item delivered'
3, 'Exchanged'
2, 'Warranty expired'

Ie I want all status.name to be present in the result, even if there were no such status during the given create_date interval.