MySQL Forums
Forum List  »  Newbie

Re: Help!!!
Posted by: Little_Cat Little_Cat
Date: April 05, 2005 12:53AM

MySQL 4.1.10a and SELECT MAX( id ) FROM Test.date return INT value, for example 4. This is Max value in Test.id field
Real project
CREATE TABLE `date` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`date` date NOT NULL default '0000-00-00',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1

CREATE TABLE `pump1` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`TVater1` float(9,3) default '0.000',
`TVater2` float(9,3) default '0.000',
`PAir1` float(9,3) default '0.000',
`PAir2` float(9,3) default '0.000',
`CurDrive` float(9,3) default '0.000',
`TFreez` float(9,3) default '0.000',
`TOutput` float(9,3) default '0.000',
`POil` float(9,3) default '0.000',
`TMag` float(9,3) default '0.000',
`owner` bigint(20) unsigned default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1

CREATE TABLE `pump2`.......
..............................................
CREATE TABLE `pump4` ......

insert ignore into pumps.date value(NULL,'2005-04-05')

insert into pumps.pump1 SET TVAter1='20.0',PAir1='30.0',owner=(select MAX(id) from pumps.date)
insert into pumps.pump1 SET TVAter1='20.0',PAir1='30.0',owner=(select MAX(id) from pumps.date)
insert into pumps.pump1 SET TVAter1='20.0',PAir1='30.0',owner=(select MAX(id) from pumps.date)
insert into pumps.pump1 SET TVAter1='20.0',PAir1='30.0',owner=(select MAX(id) from pumps.date)
insert into pumps.pump1 SET TVAter1='20.0',PAir1='30.0',owner=(select MAX(id) from pumps.date)

insert into pumps.pump2 SET TVAter1='20.0',PAir1='30.0',owner=(select MAX(id) from pumps.date)
insert into pumps.pump2 SET TVAter1='20.0',PAir1='30.0',owner=(select MAX(id) from pumps.date)
insert into pumps.pump2 SET TVAter1='20.0',PAir1='30.0',owner=(select MAX(id) from pumps.date)
insert into pumps.pump2 SET TVAter1='20.0',PAir1='30.0',owner=(select MAX(id) from pumps.date)
insert into pumps.pump2 SET TVAter1='20.0',PAir1='30.0',owner=(select MAX(id) from pumps.date)

select date.id,`pump1`.TVater1
from date RIGHT JOIN pump1 ON date.id=`pump1`.owner
where date.id=(select MAX(id) from pumps.date)

returned

id TVater1
5 20
5 20
5 20
5 20
5 20

select date.id,`pump2`.PAir1
from date RIGHT JOIN pump2 ON date.id=`pump2`.owner
where date.id=(select MAX(id) from pumps.date)

returned

id PAir1
5 30
5 30
5 30
5 30
5 30

and query
select date.id,pump1.`TVater1`,`pump2`.PAir1
from date RIGHT JOIN pump1 ON date.id=`pump1`.owner
RIGHT JOIN pump2 ON pump1.owner=pump2.`owner`
where date.id=(select MAX(id) from pumps.date)

returned
id TVater1 PAir1
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30
5 20 30

Options: ReplyQuote


Subject
Written By
Posted
April 05, 2005 12:20AM
April 05, 2005 12:31AM
Re: Help!!!
April 05, 2005 12:53AM
April 05, 2005 01:24AM
April 05, 2005 01:43AM
April 05, 2005 03:17AM
April 05, 2005 04:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.