473,322 Members | 1,719 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,322 software developers and data experts.

Haw to write query to get result in single line in DB2

3
Hi,

I would like a ask a question if it possible to get result with query in a single line. We have db2 database.

I have two tables

Matno Name
10 Jim
20 Ken
30 Jane


Matno username
10 u0500j2
10 o0600k2
20 i0500a9
30 o900j3

Result must look like this

Matno Name Username1, Username2,...
10 Jim u0500j2 o0600k2
20 Ken i0500a9
...

Is it possible to do that, please advice me, best regards
and thanks in advance, K
Aug 27 '13 #1
9 1806
redz
12
hi MKMK,
im not so sure but try this sample

Expand|Select|Wrap|Line Numbers
  1. select a.matno,a.`name`,a.username1,b.username2 from
  2. (select a.matno as matno,a.name,b.username as username1,b.username as username2 from
  3. (select * from tbl1) as a
  4. LEFT JOIN
  5. (select * from tbl2) as b on a.matno = b.matno) as a
  6.  
  7. left join
  8.  
  9. (select a.matno as matno,a.name,b.username as username1,b.username as username2 from
  10. (select * from tbl1) as a
  11. LEFT JOIN
  12. (select * from tbl2) as b on a.matno = b.matno) as b on a.matno=b.matno where a.username1<>b.username2 LIMIT 1
  13.  
Aug 28 '13 #2
Exequiel
288 256MB
Try to visit aand read this URLs , , it will solve your question. . .http://www.w3schools.com/sql/sql_join_inner.asp
http://www.w3schools.com/sql/sql_join.asp
Aug 28 '13 #3
redz
12
here's the final query..

Expand|Select|Wrap|Line Numbers
  1. select a.matno,a.`name`,a.username1 as Username1,case when a.username2=b.username1 then '' else b.username1 end as Username2 from
  2. (select a.matno as matno,a.name,b.username as username1,b.username as username2 from
  3. (select * from tbl1) as a
  4. LEFT JOIN
  5. (select * from tbl2) as b on a.matno = b.matno) as a
  6.  
  7. left join
  8.  
  9. (select a.matno as matno,a.name,b.username as username1,b.username as username2 from
  10. (select * from tbl1) as a
  11. LEFT JOIN
  12. (select * from tbl2) as b on a.matno = b.matno) as b on a.matno=b.matno group by a.name order by matno
  13.  
  14.  
  15.  
Aug 28 '13 #4
redz
12
The Result

10 Jim u0500j2 o0600k2
20 Ken i0500a9
30 Jane o900j3
Aug 28 '13 #5
MKMK
3
Hi,

Tanky very much to replay. I tried this but it return a mistake for field a.`name`. When I correct it said
for field a.username1 that is not valid in the context where it is used ;-(.

I must build a query that in one tabele is id from workers and another every worker have many usernames.

Thanks in advance, mkmk
Aug 28 '13 #6
redz
12
hi MKMK,
i used that query in MYSQL,
what type database server are you using?
here's the sample of my table according to your needs.

tbl1...

fields type length
matno int 10
name varchar 40

tbl2...
matno int 10
username varchar 40
Aug 29 '13 #7
redz
12
the field "name" is a reserved word in MYSQL but it never return an
error to me..

sometimes you have to checked what type of database server you are going to used. It unexpectedly returns an error when you used some reserved words.. It is only occurs in the name of the fields
Aug 29 '13 #8
MKMK
3
Helou,

Thanks for replay and I am sory for late response.

I am using db2 database. I will try to write your query
that my base will recognize it, I hope it will work. I let you know.

Thanks in advance, K
Sep 9 '13 #9
Anas Mosaad
185 128KB
You can also use LISTAGG to aggregate username in one single field and separate them using a separator (i.e. comma or semi-colon).
This will overcome the limitation of predefined columns. You can have arbitrary number of usernames but all represented in one single column. In your application, you may split the values using the predefined separator.
Sep 22 '13 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Joe Gazda | last post by:
I'm a relative newbie to PHP, but have been able to put together some PHP code to generate a CSV/XLS file from a Query result. Now, I would like to include custom column names instead of the MySQL...
6
by: DLP22192 | last post by:
I have the following single-line if statement that is evaluating true even though it shouldn't. I have never seen this before and I am concerned that this can happen in other areas of my code. ...
3
by: Jack | last post by:
Hi, I have a form when loaded, retrieves record from an access table. Among other fields there is a check box called FinalUpdate. This is tied to a field in Access of type Yes/No. The form...
3
by: Colleyville Alan | last post by:
I am constructing a SQL command from a function. Some code builds the WHERE clause in a looping structure and passes that as an argument to the SQL-building function. But the results do not...
16
by: Puneet | last post by:
Hi ALL, I have a silly question... (may be) Can we write a single line C program whose output is the program itself? Is anybody know the answer please tell me. Puneet
3
by: saad82 | last post by:
I want to be able to replace a single line in a large text file (several hundred MB). Using the cookbook's method (below) works but I think the replace fxn chokes on such a large chunk of text. For...
2
by: garyusenet | last post by:
I have a multi line text box, i'd like to store the contents of this textbox to a text file. However when I write the value of ' textbox.text ' to the file it is written verbatim spanning many...
2
by: reeba | last post by:
I want to store the query result, in an servlet, into an xml file and display the contents of the xml file on the browser...... my code is as follows: public void doPost(HttpServletRequest...
25
by: NDayave | last post by:
How do, I have a form that outputs addresses in a format that can be printed on to 3x7 label paper for envelopes. What I want is a way to enter blank (or " ") rows to the query result where the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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.