Query Help

[Sam Durai]

Hi All, Need help to write an SQL, requirement is as below.

db2 =select * from dealer

D_KEY NAME
----------- ----------
1 Toyota
2 Ford
3 Chrysler
4 GM

3 record(s) selected.
db2 =select * from txn

T_KEY TNAME
----------- ----------
1 Australia
5 denmark
3 Korea
2 Kenya
2 Austria
2 America
3 England
4 Finland

8 record(s) selected.

Required O/P
T_Key Name TNAME
---- ---------- -------------
1 Toyota Australia
2 Ford Kenya
3 Chrysler Korea
Thanks, Sam

[Knut Stolze]

Sam Durai wrote:

Hi All, Need help to write an SQL, requirement is as below.

db2 =select * from dealer

D_KEY NAME
----------- ----------
1 Toyota
2 Ford
3 Chrysler
4 GM

3 record(s) selected.
db2 =select * from txn

T_KEY TNAME
----------- ----------
1 Australia
5 denmark
3 Korea
2 Kenya
2 Austria
2 America
3 England
4 Finland

8 record(s) selected.

Required O/P
T_Key Name TNAME
---- ---------- -------------
1 Toyota Australia
2 Ford Kenya
3 Chrysler Korea

You should also give us a description on what you want because that is far
from clear.

One could join via the D_KEY and the T_KEY. I just don't know what the
semantics would be. And the results are a bit different, too.

SELECT t_key, name, tname
FROM txn JOIN dealer ON d_key = t_key
Maybe you want to do the same and just fetch any country:

SELECT d_key as t_key, name, ( SELECT tname
FROM dealer
FETCH FIRST 1 ROW ONLY )
FROM txm
Or maybe you require a specific row. If so, which one?
Or maybe you want to get something completely different... So please
explain the expected results.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[4.spam]

Hello.
---
select t.t_key, d.name, t.tname
from dealer d
join
(
select t_key, max(tname) tname
from txn
group by t_key
) t on d.d_key=t.t_key
order by t.t_key;
--
But this statement returns row for t_key=4.
Can you explain why you don't want to return this row?
Row for t_key=1 has the same conditions in base tables, but it is in
final result set...

Sincerely,
Mark B.

Hi All, Need help to write an SQL, requirement is as below.

db2 =select * from dealer

D_KEY NAME
----------- ----------
1 Toyota
2 Ford
3 Chrysler
4 GM

3 record(s) selected.
db2 =select * from txn

T_KEY TNAME
----------- ----------
1 Australia
5 denmark
3 Korea
2 Kenya
2 Austria
2 America
3 England
4 Finland

8 record(s) selected.

Required O/P
T_Key Name TNAME
---- ---------- -------------
1 Toyota Australia
2 Ford Kenya
3 Chrysler Korea
Thanks, Sam

[Sam Durai]

Thanks for those who responded. I'm sorry as I have not given detailed
information earlier. The requirement is that for each D_KEY in Dealer
table I need to get any row in TXN table which has the corresponding
T_KEY.

As an example When D_KEY=3 on Dealer table there are 2 matching entries
in TXN table 1) Korea & England. It I get either of this single row in
the o/p that is OK for me.

So having tested the SQL which Mark it is working according to my
requirement. Thanks Mark for hte SQL and Thanks Knut for your patient
questions as I didnt provide much information earlier.

Thanks,
Sam.
4.****@mail.ru wrote:

Hello.
---
select t.t_key, d.name, t.tname
from dealer d
join
(
select t_key, max(tname) tname
from txn
group by t_key
) t on d.d_key=t.t_key
order by t.t_key;
--
But this statement returns row for t_key=4.
Can you explain why you don't want to return this row?
Row for t_key=1 has the same conditions in base tables, but it is in
final result set...

Sincerely,
Mark B.

Hi All, Need help to write an SQL, requirement is as below.

db2 =select * from dealer

D_KEY NAME
----------- ----------
1 Toyota
2 Ford
3 Chrysler
4 GM

3 record(s) selected.
db2 =select * from txn

T_KEY TNAME
----------- ----------
1 Australia
5 denmark
3 Korea
2 Kenya
2 Austria
2 America
3 England
4 Finland

8 record(s) selected.

Required O/P
T_Key Name TNAME
---- ---------- -------------
1 Toyota Australia
2 Ford Kenya
3 Chrysler Korea
Thanks, Sam

[Tonkuma]

Here is another example:

SELECT T_Key, Name, Tname
FROM dealer
, TABLE
(SELECT T_key, Tname
FROM txn
WHERE D_key = T_key
FETCH FIRST 1 ROWS ONLY) T
ORDER BY
T_Key;

[--CELKO--]

> I'm sorry as I have not given detailed information earlier. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.