Hi folks,

sorry for the missing Name (its Christian) but the reason for my entry
here in the usenet group is not my only computer problem today...
It's late, and I have a problem that I just can't solve.

I have 2 tables that look similar to this:

table A:

ID | firstname | lastname
------------------------------------------------
1 | Thomas | Meier
2 | Hansi | Mueller
3 | Klaus | Schulze

table B:

fk_ID | date | info
------------------------------------------------
1 | 12.12.2002 | foo
1 | 11.11.2000 | bar
1 | 12.12.2003 | foo2
1 | 11.11.2005 | bar2

The output I would like to have now is the following, so only having
the NEWEST "info" entry from table B together with the data from table
A

ID | firstname | lastname | info
--------------------------------------------------------
1 | Thomas | Meier | foo
2 | Hansi | Mueller | bar2
3 | Klaus | Schulze | NULL

Is there a way to do this in one query? I just can't figure it out...

Thanks in advance!
Christian

ups_genius@gmx.net wrote:
> Hi folks,
>
> sorry for the missing Name (its Christian) but the reason for my entry
> here in the usenet group is not my only computer problem today...
> It's late, and I have a problem that I just can't solve.
>
> I have 2 tables that look similar to this:
>
> table A:
>
> ID | firstname | lastname
> ------------------------------------------------
> 1 | Thomas | Meier
> 2 | Hansi | Mueller
> 3 | Klaus | Schulze
>
> table B:
>
> fk_ID | date | info
> ------------------------------------------------
> 1 | 12.12.2002 | foo
> 1 | 11.11.2000 | bar
> 1 | 12.12.2003 | foo2
> 1 | 11.11.2005 | bar2
>
> The output I would like to have now is the following, so only having
> the NEWEST "info" entry from table B together with the data from table
> A
>
> ID | firstname | lastname | info
> --------------------------------------------------------
> 1 | Thomas | Meier | foo
> 2 | Hansi | Mueller | bar2
> 3 | Klaus | Schulze | NULL
>
> Is there a way to do this in one query? I just can't figure it out...
>
> Thanks in advance!
> Christian

What relates the data in table B to the data in table A? If it is the fk_ID,
then I would expect the output to be:

ID | firstname | lastname | info
--------------------------------------------------------
1 | Thomas | Meier | foo
2 | Hansi | Mueller | NULL
3 | Klaus | Schulze | NULL

As there are no fk_ID entries with a value of 2.

Please explain

On 4 Nov., 11:32, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
> ups_gen...@gmx.net wrote:
> > Hi folks,
>
> > sorry for the missing Name (its Christian) but the reason for my entry
> > here in the usenet group is not my only computer problem today...
> > It's late, and I have a problem that I just can't solve.
>
> > I have 2 tables that look similar to this:
>
> > table A:
>
> > ID | firstname | lastname
> > ------------------------------------------------
> > 1 | Thomas | Meier
> > 2 | Hansi | Mueller
> > 3 | Klaus | Schulze
>
> > table B:
>
> > fk_ID | date | info
> > ------------------------------------------------
> > 1 | 12.12.2002 | foo
> > 1 | 11.11.2000 | bar
> > 1 | 12.12.2003 | foo2
> > 1 | 11.11.2005 | bar2
>
> > The output I would like to have now is the following, so only having
> > the NEWEST "info" entry from table B together with the data from table
> > A
>
> > ID | firstname | lastname | info
> > --------------------------------------------------------
> > 1 | Thomas | Meier | foo
> > 2 | Hansi | Mueller | bar2
> > 3 | Klaus | Schulze | NULL
>
> > Is there a way to do this in one query? I just can't figure it out...
>
> > Thanks in advance!
> > Christian
>
> What relates the data in table B to the data in table A? If it is the fk_ID,
> then I would expect the output to be:
>
> ID | firstname | lastname | info
> --------------------------------------------------------
> 1 | Thomas | Meier | foo
> 2 | Hansi | Mueller | NULL
> 3 | Klaus | Schulze | NULL
>
> As there are no fk_ID entries with a value of 2.
>
> Please explain


Sorry, copy-paste error in table B. It is supposed to be the
following:

table B:

fk_ID | date | info
------------------------------------------------
1 | 12.12.2002 | foo
1 | 11.11.2000 | bar
2 | 12.12.2003 | foo2
2 | 11.11.2005 | bar2

I tried something that seems to work, but I am not sure if it does in
all cases. Maybe there is a better solution...?

SELECT A.id, A.firstname, A.lastname, B.date, B.info
FROM A LEFT OUTER JOIN B ON
(A.id = B.fk_ID
AND B.date = (SELECT MAX(date) FROM B where fk_ID=A.id))


Thanks!

On 4 Nov., 11:32, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
> ups_gen...@gmx.net wrote:
> > Hi folks,
>
> > sorry for the missing Name (its Christian) but the reason for my entry
> > here in the usenet group is not my only computer problem today...
> > It's late, and I have a problem that I just can't solve.
>
> > I have 2 tables that look similar to this:
>
> > table A:
>
> > ID | firstname | lastname
> > ------------------------------------------------
> > 1 | Thomas | Meier
> > 2 | Hansi | Mueller
> > 3 | Klaus | Schulze
>
> > table B:
>
> > fk_ID | date | info
> > ------------------------------------------------
> > 1 | 12.12.2002 | foo
> > 1 | 11.11.2000 | bar
> > 1 | 12.12.2003 | foo2
> > 1 | 11.11.2005 | bar2
>
> > The output I would like to have now is the following, so only having
> > the NEWEST "info" entry from table B together with the data from table
> > A
>
> > ID | firstname | lastname | info
> > --------------------------------------------------------
> > 1 | Thomas | Meier | foo
> > 2 | Hansi | Mueller | bar2
> > 3 | Klaus | Schulze | NULL
>
> > Is there a way to do this in one query? I just can't figure it out...
>
> > Thanks in advance!
> > Christian
>
> What relates the data in table B to the data in table A? If it is the fk_ID,
> then I would expect the output to be:
>
> ID | firstname | lastname | info
> --------------------------------------------------------
> 1 | Thomas | Meier | foo
> 2 | Hansi | Mueller | NULL
> 3 | Klaus | Schulze | NULL
>
> As there are no fk_ID entries with a value of 2.
>
> Please explain

Sorry, copy-paste error in table B. It is supposed to be the
following:

table B:

fk_ID | date | info
------------------------------------------------
1 | 12.12.2002 | foo
1 | 11.11.2000 | bar
2 | 12.12.2003 | foo2
2 | 11.11.2005 | bar2

I tried something that seems to work, but I am not sure if it does in
all cases. Maybe there is a better solution...?

SELECT A.id, A.firstname, A.lastname, B.date, B.info
FROM A LEFT OUTER JOIN B ON
(A.id = B.fk_ID
AND B.date = (SELECT MAX(date) FROM B where fk_ID=A.id))


Thanks!

ups_genius@gmx.net wrote:
> On 4 Nov., 11:32, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>> ups_gen...@gmx.net wrote:
>>> Hi folks,
>>
>>> sorry for the missing Name (its Christian) but the reason for my
>>> entry here in the usenet group is not my only computer problem
>>> today... It's late, and I have a problem that I just can't solve.
>>
>>> I have 2 tables that look similar to this:
>>
>>> table A:
>>
>>> ID | firstname | lastname
>>> ------------------------------------------------
>>> 1 | Thomas | Meier
>>> 2 | Hansi | Mueller
>>> 3 | Klaus | Schulze
>>
>>> table B:
>>
>>> fk_ID | date | info
>>> ------------------------------------------------
>>> 1 | 12.12.2002 | foo
>>> 1 | 11.11.2000 | bar
>>> 1 | 12.12.2003 | foo2
>>> 1 | 11.11.2005 | bar2
>>
>>> The output I would like to have now is the following, so only having
>>> the NEWEST "info" entry from table B together with the data from
>>> table A
>>
>>> ID | firstname | lastname | info
>>> --------------------------------------------------------
>>> 1 | Thomas | Meier | foo
>>> 2 | Hansi | Mueller | bar2
>>> 3 | Klaus | Schulze | NULL
>>
>>> Is there a way to do this in one query? I just can't figure it
>>> out...
>>
>>> Thanks in advance!
>>> Christian
>>
>> What relates the data in table B to the data in table A? If it is
>> the fk_ID, then I would expect the output to be:
>>
>> ID | firstname | lastname | info
>> --------------------------------------------------------
>> 1 | Thomas | Meier | foo
>> 2 | Hansi | Mueller | NULL
>> 3 | Klaus | Schulze | NULL
>>
>> As there are no fk_ID entries with a value of 2.
>>
>> Please explain
>
>
> Sorry, copy-paste error in table B. It is supposed to be the
> following:
>
> table B:
>
> fk_ID | date | info
> ------------------------------------------------
> 1 | 12.12.2002 | foo
> 1 | 11.11.2000 | bar
> 2 | 12.12.2003 | foo2
> 2 | 11.11.2005 | bar2
>
> I tried something that seems to work, but I am not sure if it does in
> all cases. Maybe there is a better solution...?
>
> SELECT A.id, A.firstname, A.lastname, B.date, B.info
> FROM A LEFT OUTER JOIN B ON
> (A.id = B.fk_ID
> AND B.date = (SELECT MAX(date) FROM B where fk_ID=A.id))
>
>
> Thanks!

Search this group for "strawberry query"
See:
http://dev.mysql.com/doc/refman/5.0/...group-row.html
for the full explanation of how it works.