On Mar 20, 7:43 pm, "Bob Bedford" <b...@bedford.com> wrote:
> as a general request, here is the script:
> SET FOREIGN_KEY_CHECKS=0;
> -- ----------------------------
> -- Table structure for client
> -- ----------------------------
> CREATE TABLE `client` (
> `idclient` mediumint(8) unsigned NOT NULL auto_increment,
> `clientname` varchar(50) NOT NULL default '',
> PRIMARY KEY (`idclient`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> -- ----------------------------
> -- Table structure for folder
> -- ----------------------------
> CREATE TABLE `folder` (
> `idfolder` mediumint(8) unsigned NOT NULL auto_increment,
> `datefolder` date default NULL,
> `idperson` mediumint(8) unsigned NOT NULL default '0',
> `idclient` mediumint(9) NOT NULL default '0',
> PRIMARY KEY (`idfolder`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> -- ----------------------------
> -- Table structure for folderstatus
> -- ----------------------------
> CREATE TABLE `folderstatus` (
> `idfolderstatus` mediumint(8) unsigned NOT NULL auto_increment,
> `idfolder` mediumint(8) unsigned NOT NULL default '0',
> `idtypestatus` mediumint(8) unsigned NOT NULL default '0',
> PRIMARY KEY (`idfolderstatus`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> -- ----------------------------
> -- Table structure for person
> -- ----------------------------
> CREATE TABLE `person` (
> `idperson` mediumint(8) unsigned NOT NULL auto_increment,
> `personname` varchar(50) NOT NULL default '',
> PRIMARY KEY (`idperson`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> -- ----------------------------
> -- Table structure for typestatus
> -- ----------------------------
> CREATE TABLE `typestatus` (
> `idtypestatus` mediumint(8) unsigned NOT NULL auto_increment,
> `typestatus` varchar(50) NOT NULL default '',
> `idcheck` tinyint(1) unsigned NOT NULL default '0',
> PRIMARY KEY (`idtypestatus`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> -- ----------------------------
> -- Records
> -- ----------------------------
> INSERT INTO `client` VALUES ('1', 'client1');
> INSERT INTO `client` VALUES ('2', 'client2');
> INSERT INTO `client` VALUES ('3', 'client3');
> INSERT INTO `client` VALUES ('4', 'client4');
> INSERT INTO `client` VALUES ('5', 'client5');
> INSERT INTO `folder` VALUES ('1', '2006-01-01', '1', '1');
> INSERT INTO `folder` VALUES ('2', '2006-02-05', '1', '3');
> INSERT INTO `folder` VALUES ('3', '2006-03-08', '2', '1');
> INSERT INTO `folder` VALUES ('4', '2006-03-09', '2', '2');
> INSERT INTO `folder` VALUES ('5', '2006-05-05', '2', '3');
> INSERT INTO `folder` VALUES ('6', '2006-12-08', '3', '2');
> INSERT INTO `folderstatus` VALUES ('1', '1', '1');
> INSERT INTO `folderstatus` VALUES ('2', '1', '2');
> INSERT INTO `folderstatus` VALUES ('3', '1', '3');
> INSERT INTO `folderstatus` VALUES ('4', '2', '1');
> INSERT INTO `folderstatus` VALUES ('5', '2', '3');
> INSERT INTO `folderstatus` VALUES ('6', '2', '4');
> INSERT INTO `folderstatus` VALUES ('7', '3', '4');
> INSERT INTO `folderstatus` VALUES ('8', '3', '1');
> INSERT INTO `folderstatus` VALUES ('9', '4', '1');
> INSERT INTO `folderstatus` VALUES ('10', '4', '4');
> INSERT INTO `folderstatus` VALUES ('11', '4', '3');
> INSERT INTO `folderstatus` VALUES ('12', '1', '3');
> INSERT INTO `folderstatus` VALUES ('13', '1', '3');
> INSERT INTO `person` VALUES ('1', 'aaaaa');
> INSERT INTO `person` VALUES ('2', 'bbbbbb');
> INSERT INTO `person` VALUES ('3', 'cccccc');
> INSERT INTO `person` VALUES ('4', 'ddddd');
> INSERT INTO `person` VALUES ('5', 'eeeeee');
> INSERT INTO `person` VALUES ('6', 'ffffff');
> INSERT INTO `person` VALUES ('7', 'gggggg');
> INSERT INTO `typestatus` VALUES ('1', 'status1', '1');
> INSERT INTO `typestatus` VALUES ('2', 'status2', '1');
> INSERT INTO `typestatus` VALUES ('3', 'status3', '1');
> INSERT INTO `typestatus` VALUES ('4', 'status4', '0');
> INSERT INTO `typestatus` VALUES ('5', 'status5', '1');
>
> The query (seems to work)
> select person.idperson,
> folder.datefolder, folder.idclient, folder.idfolder
> from folderstatus
> inner join typestatus on folderstatus.idtypestatus=typestatus.idtypestatus
> inner join folder on folderstatus.idfolder = folder.idfolder
> inner join person on folder.idperson = person.idperson
> inner join client on folder.idclient = client.idclient
> group by folder.idfolder
> having (max(folderstatus.idtypestatus) = (select idtypestatus from
> typestatus where typestatus.typestatus like '%4%' limit 0,1)
> and folder.datefolder between '2006-01-01' and '2007-01-01')
>
> But what I don't know how to do now:
> I'd like all the records from folderstatus where last (I mean bigger
> idfolderstatus) for every folder linked to the typestatus has a
> typestatus.idcheck to 0.
>
> Let's explain again in pseudo-code:
> select * from folderstatus inner join typestatus where folderstatus =
> max(folderstatus for this folder) and folderstatus.idcheck = 0
> So if max(folderstatus) = 4 in this case, then return idfolder (as idcheck
> in typestatus = 0) else don't return the row.
> How to do this ?
>
> Thanks for help.
>
> Bob

Have`you tried looking at the ubiquitous "strawberry query" in this
and other newsgroups?