MySQL Forums
Forum List  »  Newbie

Help
Posted by: Little_Cat Little_Cat
Date: April 09, 2005 03:39AM

Hi! Sorry for another time, but still got problems with grouping. DB MySQL-4.1.10a, WindowsXP+SP2RUS

There's DB Pumps, represented by 6 tables.

Table 1 – date

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



Table 2 –Pump1

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



The rest of tables are identical with Pump1



The table date has records (for example):



id date

1 01.04.2005

2 02.04.2005

3 03.04.2005

4 04.04.2005

5 05.04.2005



Table Pump1 has records

id TVater1 TVater2 PAir1 PAir2 CurDrive TFreez TOutput POil TMag owner

1 1,1 0 0 0 0 0 0 0 0 4

2 2,1 0 0 0 0 0 0 0 0 4

3 3,1 0 0 0 0 0 0 0 0 4

4 4,1 0 0 0 0 0 0 0 0 4

5 5,1 0 0 0 0 0 0 0 0 4

6 5,1 0 0 0 0 0 0 0 0 1

7 5,1 0 0 0 0 0 0 0 0 1

8 5,1 0 0 0 0 0 0 0 0 1

9 5,1 0 0 0 0 0 0 0 0 1

10 5,1 0 0 0 0 0 0 0 0 1

11 20 10 30 30 0 0 0 0 0 5

12 20 10 30 30 0 0 0 0 0 5

13 20 10 30 30 0 0 0 0 0 5

14 20 10 30 30 0 0 0 0 0 5

15 20 10 30 30 0 0 0 0 0 5



Table Pump3 has records

id TVater1 TVater2 PAir1 PAir2 CurDrive TFreez TOutput POil TMag owner

1 1,1 0 0 0 0 0 0 0 0 4

2 1,2 0 0 0 0 0 0 0 0 4

3 1,3 0 0 0 0 0 0 0 0 4

4 1,4 0 0 0 0 0 0 0 0 4

5 1,5 0 0 0 0 0 0 0 0 4

6 5,1 0 0 0 0 0 0 0 0 1

7 5,1 0 0 0 0 0 0 0 0 1

8 5,1 0 0 0 0 0 0 0 0 1

9 5,1 0 0 0 0 0 0 0 0 1

10 5,1 0 0 0 0 0 0 0 0 1

11 20 10 30 30 0 0 0 0 0 5

12 20 10 30 30 0 0 0 0 0 5

13 20 10 30 30 0 0 0 0 0 5

14 20 10 30 30 0 0 0 0 0 5

15 20 10 30 30 0 0 0 0 0 5



The example of query:

SELECT date.id, pump1.TVater1, `pump3`.TVater1

FROM date

LEFT JOIN pump1 ON pump1.owner = date.id

LEFT JOIN pump3 ON `pump3`.owner=date.id

WHERE date.id = ( SELECT date.id FROM date where date=(Required date is here ) )

GROUP BY pump1.id,date.id



But - for example with id=4,

the response is




Id TVater1 TVater1_1

4 1.1 1.1

4 2.1 1.1

4 3.1 1.1

4 4.1 1.1

4 5.1 1.1

so the data from table 3 are represented only with the first record, instead of all of them.

Please, how can I make a query to get data from any table by single date.id - owner.

The data is recorded to tables synchroneously.

Options: ReplyQuote


Subject
Written By
Posted
Help
April 09, 2005 03:39AM
April 09, 2005 05:05AM


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.