Sql DB2

[TThai]

Hi,
Can someone help me please get the result i need. I have two tables
(tableA and tableB). These tables are linked thru field2. I would like
to get the last record in TableA (field1 = 002) but with field3 value
'abc' NOT dashes matches with TableA.field2 = TableB.field2. The
records in TableA are linked thru TableA.field1 = TableA.field4. The
maximum records created in TableA for each TableB record is two.

TableA:
field1 field2 field3 field4
001 0 abc 0
002 2222 ---- 001

TableB:
field1 field2
001 2222

Appreciated in advance for any help.

Sincerely,
Teresa

[Stefan M. Mihokovic]

Re: Sql DB2

Hallo Teresa,



I am me not sure that I understand yours question correctly.

An example in more detail would be helpful.

But I believe this could solve your problem.





select TableA_1.field1

, TableA_1.field2

, TableA_2.field3

, TableA_1.field4

from TableB

inner join

TableA as TableA_1

on TableB.field2 = TableA_1.field2

inner join

TableA as TableA_2

on TableA_1.field4 = TableA_2.field1

;





Regards Stefan

----------------------------------
Stefan M. Mihokovic
email: stemi@stemi.de
----------------------------------

[Jean-David Beyer]

Re: Sql DB2

TThai wrote:[color=blue]
> Hi,
> Can someone help me please get the result i need. I have two tables
> (tableA and tableB). These tables are linked thru field2. I would like
> to get the last record in TableA (field1 = 002) but with field3 value
> 'abc' NOT dashes matches with TableA.field2 = TableB.field2. The
> records in TableA are linked thru TableA.field1 = TableA.field4. The
> maximum records created in TableA for each TableB record is two.
>
> TableA:
> field1 field2 field3 field4
> 001 0 abc 0
> 002 2222 ---- 001
>
> TableB:
> field1 field2
> 001 2222
>
> Appreciated in advance for any help.
>
> Sincerely,
> Teresa[/color]

When you say "last record", is that just for your example?
Because there is no ordering in a relational database unless
you put an "ORDER BY" in your SELECT.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 73926.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 8:45am up 33 days, 7:19, 2 users, load average: 2.41, 2.45, 2.30

[Tokunaga T.]

Re: Sql DB2

I added some more data for your sample.
SELECT * FROM TableA;
---------------------------------------------------

FIELD1 FIELD2 FIELD3 FIELD4
------ ------ ------ ------
1 0 abc 0
2 2222 ---- 1
3 0 def 0
4 3333 ghi 3
5 4444 jkl 0
6 5555 ---- 0

6 record(s) selected.


SELECT * FROM TableB;
---------------------------------------------------

FIELD1 FIELD2
------ ------
1 2222
2 3333
3 4444
4 5555

4 record(s) selected.


My guess for results is....
FIELD1 FIELD3
------ ------
1 abc
2 ghi
3 jkl
4 ----


Are these right?
I'm not shure the result for TableB.field1 = 004.
Because number of corresponding rows in TableA is one and the value of
field3 of that row is dashes.

Anyway, how about this?
SELECT B.field1
, COALESCE(A2.fie ld3, A1.field3) AS field3
FROM TableB B
INNER JOIN
TableA A1
ON A1.field2 = B.field2
LEFT OUTER JOIN
TableA A2
ON A2.field1 = A1.field4
AND A1.field3 = '----'
;
---------------------------------------------------

FIELD1 FIELD3
------ ------
1 abc
2 ghi
3 jkl
4 ----

4 record(s) selected.

[TThai]

Re: Sql DB2

"Stefan M. Mihokovic" <news@stemi.d e> wrote in message news:<bq1l6s$bq j$07$1@news.t-online.com>...[color=blue]
> Hallo Teresa,
>
>
>
> I am me not sure that I understand yours question correctly.
>
> An example in more detail would be helpful.
>
> But I believe this could solve your problem.
>
>
>
>
>
> select TableA_1.field1
>
> , TableA_1.field2
>
> , TableA_2.field3
>
> , TableA_1.field4
>
> from TableB
>
> inner join
>
> TableA as TableA_1
>
> on TableB.field2 = TableA_1.field2
>
> inner join
>
> TableA as TableA_2
>
> on TableA_1.field4 = TableA_2.field1
>
> ;
>
>
>
>
>
> Regards Stefan
>
> ----------------------------------
> Stefan M. Mihokovic
> email: stemi@stemi.de
> ----------------------------------[/color]


APPRECIATED EVERYONE'S RESPONSE.

TERESA