SEO

Hi all, on a query I've this result:
69 86
77 84
112 91
114 91
119 91
124 92
125 92
133 93
....

first is the idfolder, second is the idclient.

Now When I do a group by idfolder, I've a total of 77 rows (see the result
above for the first part). If I do a group by idclient, then I only get 3
results

Why ? Have you an idea ?

Here is the query:
select folder.idfolder, folder.idclient from folderstatus
inner join statustype on folderstatus.idstatustype=statustype.idstatus
inner join folder on folderstatus.iddossier = folder.idfolder
group by folder.idfolder
having max(idstatustype) = (select idstatus from statustype where
statustype.name like '%search%' limit 0,1)

if I do a group by folder.idclient, then I only get 3 rows instead of about
50.

Bob

On 20 Mar, 15:38, "Bob Bedford" <b...@bedford.com> wrote:
> Hi all, on a query I've this result:
> 69 86
> 77 84
> 112 91
> 114 91
> 119 91
> 124 92
> 125 92
> 133 93
> ...
>
> first is the idfolder, second is the idclient.
>
> Now When I do a group by idfolder, I've a total of 77 rows (see the result
> above for the first part). If I do a group by idclient, then I only get 3
> results
>
> Why ? Have you an idea ?
>
> Here is the query:
> select folder.idfolder, folder.idclient from folderstatus
> inner join statustype on folderstatus.idstatustype=statustype.idstatus
> inner join folder on folderstatus.iddossier = folder.idfolder
> group by folder.idfolder
> having max(idstatustype) = (select idstatus from statustype where
> statustype.name like '%search%' limit 0,1)
>
> if I do a group by folder.idclient, then I only get 3 rows instead of about
> 50.
>
> Bob

Not easy to tell if we don't know what data is in he tables.

> Not easy to tell if we don't know what data is in he tables.

I can't export datas as they are confidential.
anyway the result are strange when I group by a field like it doesn't take
all results.
Is the problem from "having" ?

On Mar 20, 4:01 pm, "Bob Bedford" <b...@bedford.com> wrote:
> > Not easy to tell if we don't know what data is in he tables.
>
> I can't export datas as they are confidential.
> anyway the result are strange when I group by a field like it doesn't take
> all results.
> Is the problem from "having" ?

Why not present some dummy data (5 - 10 rows, say) together with the
results you'd expect from that data. Then we'd have a chance, (but
don't make it too easy!)

On Tue, 20 Mar 2007 16:38:36 +0100, Bob Bedford wrote:
> Hi all, on a query I've this result:
> 69 86
> 77 84
> 112 91
> 114 91
> 119 91
> 124 92
> 125 92
> 133 93
> ...
>
> first is the idfolder, second is the idclient.
>
> Now When I do a group by idfolder, I've a total of 77 rows (see the result
> above for the first part). If I do a group by idclient, then I only get 3
> results
>
> Why ? Have you an idea ?
>
> Here is the query:
> select folder.idfolder, folder.idclient from folderstatus
> inner join statustype on folderstatus.idstatustype=statustype.idstatus
> inner join folder on folderstatus.iddossier = folder.idfolder
> group by folder.idfolder
> having max(idstatustype) = (select idstatus from statustype where
> statustype.name like '%search%' limit 0,1)
>
> if I do a group by folder.idclient, then I only get 3 rows instead of about
> 50.

You haven't told us anything about your statustype table, its contents,
etc, yet, and you're selecting only SOME of the records, based on that.
How many, we certainly cannot say.

--
Cunningham's Second Law:
It's always more complex than you expect, even when you take
Cunningham's Second Law into account.

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

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?

tried this for my other post, but return 0 values:

select person.idperson, folderstatus.idtypestatus,typestatus.idcheck,
folder.datefolder, folder.idclient, folder.idfolder
from typestatus
inner join folderstatus on typestatus.idtypestatus =
folderstatus.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 (folderstatus.idtypestatus = max(folderstatus.idtypestatus) and
typestatus.idcheck = 0)

Can't find in this NG. Google didn't help much.

add this to the script:
INSERT INTO `folderstatus` VALUES ('14', '3', '5');
update `typestatus` set idcheck = 0 where idtypestatus = 5;

Now the query:
select folder.idperson, folderstatus.idtypestatus,typestatus.idcheck,
folderstatus.idfolderstatus,
folder.datefolder, folder.idfolder
from typestatus
inner join folderstatus on typestatus.idtypestatus =
folderstatus.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
where typestatus.idcheck = 0
group by idtypestatus, idfolder
having (idfolderstatus = max(folderstatus.idfolderstatus))

I've 4 results but IdFolder 3 is shown twice and I only want the bigger
idfolderstatus for every folder.

How to do so ? I seem close to the final result, I've tried to group by
different manners but no way. Something's wrong and I'm not a MySQL gourou,
so please help me.

Bob