473,327 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 1618
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
7
by: Simon Bailey | last post by:
How do you created a query in VB? I have a button on a form that signifies a certain computer in a computer suite. On clicking on this button i would like to create a query searching for all...
4
by: d.p. | last post by:
Hi all, I'm using MS Access 2003. Bare with me on this description....here's the situation: Imagine insurance, and working out premiums for different insured properties. The rates for calculating...
4
by: Alan Lane | last post by:
Hello world: I'm including both code and examples of query output. I appologize if that makes this message longer than it should be. Anyway, I need to change the query below into a pivot table...
36
by: Liam.M | last post by:
hey guys, I have one last problem to fix, and then my database is essentially done...I would therefore very much appreciate any assistance anyone would be able to provide me with. Currently I...
5
by: elitecodex | last post by:
Hey everyone. I have this query select * from `TableName` where `SomeIDField` 0 I can open a mysql command prompt and execute this command with no issues. However, Im trying to issue the...
10
by: aaronrm | last post by:
I have a real simple cross-tab query that I am trying to sum on as the action but I am getting the "data type mismatch criteria expression" error. About three queries up the food chain from this...
11
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction....
4
by: Doris | last post by:
It does not look like my message is posting....if this is a 2nd or 3rd message, please forgive me as I really don't know how this site works. I want to apologize ahead of time for being a novice...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.