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
9 1806
hi MKMK,
im not so sure but try this sample -
select a.matno,a.`name`,a.username1,b.username2 from
-
(select a.matno as matno,a.name,b.username as username1,b.username as username2 from
-
(select * from tbl1) as a
-
LEFT JOIN
-
(select * from tbl2) as b on a.matno = b.matno) as a
-
-
left join
-
-
(select a.matno as matno,a.name,b.username as username1,b.username as username2 from
-
(select * from tbl1) as a
-
LEFT JOIN
-
(select * from tbl2) as b on a.matno = b.matno) as b on a.matno=b.matno where a.username1<>b.username2 LIMIT 1
-
here's the final query.. -
select a.matno,a.`name`,a.username1 as Username1,case when a.username2=b.username1 then '' else b.username1 end as Username2 from
-
(select a.matno as matno,a.name,b.username as username1,b.username as username2 from
-
(select * from tbl1) as a
-
LEFT JOIN
-
(select * from tbl2) as b on a.matno = b.matno) as a
-
-
left join
-
-
(select a.matno as matno,a.name,b.username as username1,b.username as username2 from
-
(select * from tbl1) as a
-
LEFT JOIN
-
(select * from tbl2) as b on a.matno = b.matno) as b on a.matno=b.matno group by a.name order by matno
-
-
-
The Result
10 Jim u0500j2 o0600k2
20 Ken i0500a9
30 Jane o900j3
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
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
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
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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.
...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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
|
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...
| |