473,324 Members | 2,535 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,324 software developers and data experts.

joining results in single record?

I have a table with 2 records:

==========
TABLE: users
==========

name_id (INT)
name (varchar)
code (int)

Inside the table I have a few records:

id name code
-----------------
1. John 233
2. Fred 211
3. John 122
4. John 555
5. Fred 444

(this is a simplified example of the actual tables so bare with me)

Now I want to select all users and COMBINE the codes and INSERT them into a
new table like this:

=============
TABLE: combined
=============

id (INT)
name (varchar)
combined_codes (varchar)

id name combined_codes
-----------------------------
1. John 233, 122, 55
2. Fred 211, 444

I have tried the CONCAT_WS statement, but code like "CONCAT_WS(',', code,
code) only combines 2 of the same codes.

Is there a way to do this?

Tnx.
Jul 20 '05 #1
1 1553
Ondernemer wrote:
I have a table with 2 records:

==========
TABLE: users
==========

name_id (INT)
name (varchar)
code (int)

Inside the table I have a few records:

id name code
-----------------
1. John 233
2. Fred 211
3. John 122
4. John 555
5. Fred 444

(this is a simplified example of the actual tables so bare with me)

Now I want to select all users and COMBINE the codes and INSERT them into a
new table like this:

=============
TABLE: combined
=============

id (INT)
name (varchar)
combined_codes (varchar)

id name combined_codes
-----------------------------
1. John 233, 122, 55
2. Fred 211, 444

I have tried the CONCAT_WS statement, but code like "CONCAT_WS(',', code,
code) only combines 2 of the same codes.

Is there a way to do this?

Tnx.


I'd do it in PHP or some other coding language like this:
(1)create a new table (to handle combined situation).
(2)Issue a select distinct on user name + code in your first table.
Order by username.
(3)using PHP, cycle through the set and collect the data with a simple
algorithm. Use a previous/current variable to flag a change in
username. if no change, keep concatenating the $combined_value variable.
(4) when a change occurs, write out a single line: containing the
(previous, before the change)username and concatenated variable to your
new combined table.

There's probably a way to do it with just SQL, but why fry your brain on
that route? My general rule of thumb is that the more complex the
logic, the more it begs to be done outside of SQL. :-)

-Paul Bramscher
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: James | last post by:
Can anyone please shed some light on the following... I have a framework that uses dynamically created tables, named using an incremental "attribute set ID", as follows: attrdata_1 attrdata_2...
4
by: Larry | last post by:
OK, I'm just learning MySQL, or at least trying to. I have a table with data as follows USER FIELDID VALUE 1 1 Bob 1 2 Smith 2 1 John 2 ...
2
by: phillip.s.powell | last post by:
SELECT s.id, s.student_first_name, s.student_last_name, IF(s.student_ethnicity_interest_other IS NOT NULL AND s.student_ethnicity_interest_other != '', CONCAT(s.student_ethnicity_interest_other,...
2
by: mdallison | last post by:
How can I join two tables I have and keep the columns independently distinct. For example, say i have the folowing tables: table1: station_id sequence ------------------------------- 1...
1
by: aknoch | last post by:
My basic situation is this - I ONLY want duplicates, so the opposite of DISTINCT: I have two tables. Ordinarily, Table1ColumnA corresponds in a one to one ratio with Table2ColumnB through a...
1
by: pmouseca | last post by:
Hello all, I have two tables, one call Assignments, the other call Grades. In Assignment, the columns are: Asn_ID, Asn_Name, Asn_MaxGrade In Grades, the columns are: Grade_ID,...
2
by: Supermansteel | last post by:
I am joining these 2 tables together in Access 2003 and can't figure out the exact way of writing this script......Can anyone help? I have the following SQL: SELECT...
4
by: rhino | last post by:
I'm very new to XML and maybe just a touch impatient because I'm going to ask a moderately advanced question even though I'm just learning the basics. I've spent many years working with...
3
by: Ken Fine | last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets and DataTables should be able to answer fairly easily. The basic question is how I can efficiently match data from 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...
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: 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
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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.