470,591 Members | 2,085 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,591 developers. It's quick & easy.

Query Help

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

Aug 3 '06 #1
5 1526
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
Aug 3 '06 #2
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
Aug 3 '06 #3
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
Aug 4 '06 #4
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;

Aug 5 '06 #5
> 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.

Aug 5 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by netpurpose | last post: by
7 posts views Thread by Simon Bailey | last post: by
36 posts views Thread by Liam.M | last post: by
4 posts views Thread by Doris | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.