Subquery or what ?

Bill

unread,

Jul 20, 2008, 9:55:45 AM7/20/08

to

Hi

Ubuntu 7.10 Mysql 5

Given these 2 tables:
-----------------------------

CREATE TABLE `bateaux_copy` (

`id` int(10) unsigned NOT NULL auto_increment,

`annee` int(10) unsigned NOT NULL,

`nom` varchar(254) character set utf8 collate utf8_unicode_ci
NOT NULL default ' ',
`tonnage` int(10) unsigned default NULL,

`port` varchar(254) character set utf8 collate
utf8_unicode_ci NOT NULL default ' ',
`depart` varchar(254) character set utf8 collate
utf8_unicode_ci NOT NULL default ' ',
`dest` varchar(254) character set utf8 collate
utf8_unicode_ci NOT NULL default ' ',
`notes` text character set utf8 collate utf8_unicode_ci,

`capitaine` varchar(254) character set utf8 collate
utf8_unicode_ci NOT NULL default ' ',
PRIMARY KEY (`id`)

)

CREATE TABLE `passagers` (

`id` int(10) unsigned NOT NULL auto_increment,

`annee` int(11) default NULL,

`bateau` int(11) default NULL,

`prenom` varchar(254) character set utf8 collate
utf8_unicode_ci NOT NULL default ' ',
`nom` varchar(254) character set utf8 collate
utf8_unicode_ci NOT NULL default ' ',
`metier` varchar(254) character set utf8 collate
utf8_unicode_ci NOT NULL default ' ',
`origine` varchar(254) character set utf8 collate
utf8_unicode_ci NOT NULL default ' ',
PRIMARY KEY (`id`)

)
------------------
I want to produce a list of all "passagers" gouped by passagers.annee
and passagers.bateau replacing passagers.bateau by bateaux.name without
using subroutines like while(){while(){While()}}}

I tried different subselect but without success.

Is it possible?

Thanks

Michael Austin

unread,

Jul 20, 2008, 10:21:32 AM7/20/08

to

What have you tried? An example query with sample data, result you are
getting and what the expected result should be.

sheldonlg

unread,

Jul 20, 2008, 10:26:07 AM7/20/08

to

Assuming that bateaux is a foreign key in passengers that matches the id
in bateaux_copy:

select passengers.annee, bateaux_copy.nom from passengers
join bateachx_copy on (beteaux_copy.id = passengers.bateaux)
group by passengers.annee

(untested)

Bill

unread,

Jul 20, 2008, 11:23:09 AM7/20/08

to

Michael Austin a écrit :

'select passagers.*, bateaux.id as bid, bateaux.nom as bnom from
passagers join bateaux on (bateaux.id=passagers.bateau) order by
passagers.annee, passagers.bateau'

This leads to a list ordered as expected but with fields bnom and annee
repeated.

I expect :

1989
Titanic
McBean, Jonathan laborer London,Eng
Darmouth, Jos. mechanic eng. Australia
...
Blue Nose
Jefferson, Thomas president USA
...

1990
Titanic
Buddy, Jos terrorist Russia
...

Erick T. Barkhuis

unread,

Jul 20, 2008, 11:29:39 AM7/20/08

to

Bill:

> 'select passagers.*, bateaux.id as bid, bateaux.nom as bnom from
> passagers join bateaux on (bateaux.id=passagers.bateau) order by
> passagers.annee, passagers.bateau'
>
> This leads to a list ordered as expected but with fields bnom and annee
> repeated.
>
> I expect :
>
> 1989
> Titanic
> McBean, Jonathan laborer London,Eng
> Darmouth, Jos. mechanic eng. Australia
> ...
> Blue Nose
> Jefferson, Thomas president USA
> ...
>

> ...

What exactly do you expect in the annee field for Blue Nose? Do you want
"nothing" in that field?

--
Erick

Bill

unread,

Jul 20, 2008, 11:31:42 AM7/20/08

to

Michael Austin a écrit :

Bateaux.id = passagers.bateau

Bill

unread,

Jul 20, 2008, 11:38:43 AM7/20/08

to

Erick T. Barkhuis a écrit :

The Bateaux table is a node.
The only relevant information here is bateaux.id that corresponds to
passagers.bateau and bateaux.nom as far as the expected list is
concerned. Nothing to do with the request here.

The list gives passengers year of arrival, ship they were on and some
general interest passenger infos as job and where they com from.

Bill

unread,

Jul 20, 2008, 11:52:17 AM7/20/08

to

Erick T. Barkhuis a écrit :

It's a list of passengers arrival by ship by year.

Erick T. Barkhuis

unread,

Jul 20, 2008, 11:59:14 AM7/20/08

to

Bill:

I understand that. It's your "...but with fields bnom and annee
repeated..." which I don't get.

In my perception, you should get something like:

1989 | Titanic | McBean, Jonathan | laborer | London,Eng

etc.

Isn't that what you want for your application, which would turn this into
the presentation that you want to offer the end user?

--
Erick

Bill

unread,

Jul 20, 2008, 12:45:43 PM7/20/08

to

>>>> This leads to a list ordered as expected but with fields bnom and annee
>>>> repeated.
>>>>
>>>> I expect :
>>>>
>>>> 1989
>>>> Titanic
>>>> McBean, Jonathan laborer London,Eng
>>>> Darmouth, Jos. mechanic eng. Australia
>>>> ...
>>>> Blue Nose
>>>> Jefferson, Thomas president USA
>>>> ...
>>>>
>>>> ...
>>>
>>> What exactly do you expect in the annee field for Blue Nose? Do you want
>>> "nothing" in that field?
>>>
>> It's a list of passengers arrival by ship by year.
>
> I understand that. It's your "...but with fields bnom and annee
> repeated..." which I don't get.

In the request ... 'select bateaux.nom as bnom'. bnom is then the name
of the field I'll use to retrieve the related data.

Exactly.