Hi All,
I am attempting to merge something like this in my MySQL database:
Name | Dept
=========
John Account
Wayne Account
James Management
Bond Management
into something like this:
Name | Dept
=========
John,Wayne Account
James,Bond Management
So far, I have found that by running the MySQL Script: -
SELECT dept,
-
GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ',')
-
FROM Table
-
GROUP BY dept
will do the stuff I need in getting the data out, but how do I then run a insert / update to insert it into the column Name?
Any help is greatly appreciated. Thanks in advance.
15 27036 Atli 5,058
Recognized Expert Expert
Hi.
You really, really shouldn't try to do that.
You should never put more than one piece of data into a single field. It is a very bad idea, because in that format, your database has no control over it.
MySQL will consider it all to be a single string, which puts your data at a much greater risk of getting corrupted, and makes it that much more complex for the front-end software to actually use the data.
I suggest you check out this article. It explains the 3 basic rules you should always keep in mind when designing a database. http://bytes.com/topic/access/insigh...ble-structures.
Hi Atli,
Thanks for the reply. I do understand the basic principle behind database design, however, in this case, the software I am using required that the data be combined into a single row in a single column for it to function, thus the question on how to do it... :)
I've so far been able to work out how to use GROUP_CONCAT, but unfortunately, how to tie in updating the table's column and row with the new data is still beyond me.
If i was to use a temp table, will it help? eg:
Table1
NAME | DEPT
==========
JOHN ACCOUNT
DOE ACCOUNT
JANE MANAGEMENT
DOE MANAGEMENT
with the new data combined into a new table like:
Table2
NAME2 | DEPT2
===========
JOHN,DOE ACCOUNT
JANE,DOE MANAGEMENT
Introducing the other table will give rise to the need to synchronize it's contents with the original tables. If your application requires the fields to be concatenated for view purposes only then there is no need to change the tables. For the update part, your front end language should catch the data and convert it into the format required by the back end.
Basically database design should be done independently of how the data needs to be presented. In this case I think that you are trying to change the back end to satisfy a presentation format which should never be done.
Hi,
Perhaps a more detail description of the issue i'm facing will help everyone understand why I'm trying to do it. :)
I have a Postfix server which is dependent on the MySQL database backend. However, to create a mass mailing list that is auto updated, I am trying to write a sql script to grip the data from the the mailbox table and update the alias table sorted by domain.
As i have tried to do so from the applications and it has never worked (postfixadmin) and found out that the previous sysadmin also use the method of copy and paste all the address into the rows to update it.
So in brief, I'm trying to grip from :
Mailbox
username | domain
============== abc@abc.com abc.com def@abc.com abc.com
and combine the data into a single row in the alias table:
Alias
goto | domain
==========
abc@abc.com,def @abc.com abc.com
So I truely hope you guys can help me out on this, as I'm at a lost on how to go about it. :(
Atli 5,058
Recognized Expert Expert
You could try using a view. That way you wouldn't have to mutilate the actual data but you could still convert it into whatever format your software needs.
So, using the query you posted in the first post: -
CREATE VIEW `nameListView` AS
-
SELECT dept,
-
GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ',')
-
FROM Table
-
GROUP BY dept
-
You should be able to treat `nameListView` as an actual table, which would return the data precisely the way your wanted it.
So in this case, if i use the example for my DB: -
CREATE VIEW `nameListView` AS
-
SELECT domain,
-
GROUP_CONCAT(username ORDER BY username ASC SEPARATOR ',')
-
FROM mailbox
-
GROUP BY domain
followed by: -
UPDATE alias
-
SET goto = nameListView
Will that update the goto column in the alias table with the data?
Atli 5,058
Recognized Expert Expert
No. The `nameListView` VIEW acts as a regular table.
Meaning you don't actually have to pull the data into a temporary table. Just querying the VIEW will work.
So you can do: - SELECT * FROM nameListView
And get the results of the query you created the VIEW with, just like if you had create a table using the data from the query.
So if you need a table named `alias` that should be structured like the return data of a query, create a VIEW named `alias` instead using the query.
Then simply have your software query the `alias` VIEW.
You can even set the "field" names of the *fake* table your VIEW represents by using the AS clause in the query that creates it.
For example, if you have this view: -
CREATE VIEW `TimeAndDate` AS
-
SELECT
-
DATE(NOW()) AS 'CurrentDate',
-
TIME(NOW()) AS 'CurrentTime';
-
And you do: - SELECT * FROM `TimeAndDate`
You would get: -
+-------------+-------------+
-
| CurrentDate | CurrentTime |
-
+-------------+-------------+
-
| 2009-01-08 | 19:51:42 |
-
+-------------+-------------+
Ah ok. Understand.
If possible, I belief I will need the data to be written to an actual table call 'alias' into an actual row 'goto', as the program (postfix) are relatively hard coded and I don't have the ability to change the codes in it.
I do know that the general practice is to use view for select and display, but in this case, as the table is also used by other parts of the program which require the same format of data (ie. concated longtext), I belief it would be easier if it was done this way, as the alternative will be to rewrite the whole postfix mail program.
So basically, I need to grip the 'username' from the table 'mailbox' grouped by 'domain' and format it into a long string separated by ',' and insert the whole string into a field call 'goto' in the 'alias' table which has the same domain as mailbox's domain.
Hope you guys can help me with this... :)
Atli 5,058
Recognized Expert Expert
OK. Personally, I would use a View to do this. Your software shouldn't be able to tell the difference between an actual table or a VIEW.
But, if you want a table, that is possible to.
You can either create the table using a SELECT statement: -
CREATE TABLE `alias` AS
-
SELECT
-
`domain`,
-
GROUP_CONCAT(`username` ORDER BY `username` DESC SEPARATOR ',') AS 'goto'
-
FROM `mailbox`
-
GROUP BY `domain`;
Note, the fields in the new table would be named after the rows the SELECT returns, which is why I added the AS keyword to the GROUP_CONCAT
Or create the table first, and insert the data into it: -
INSERT INTO `alias`(`domain`, `goto`)
-
SELECT
-
`domain`,
-
GROUP_CONCAT(`username` ORDER BY `username` DESC SEPARATOR ',') AS 'goto'
-
FROM `mailbox`
-
GROUP BY `domain`;
-
Note that the names of the rows returned by the SELECT must be the same as the names of the fields, or you will get an incorrect column count error
But...
This VIEW should be able to replace the tables created by both of those methods: -
CREATE VIEW `alias` AS
-
SELECT
-
`domain`,
-
GROUP_CONCAT(`username` ORDER BY `username` DESC SEPARATOR ',') AS 'goto'
-
FROM `mailbox`
-
GROUP BY `domain`;
I recommend you try that first, just to see if it will work :)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: David |
last post by:
I'm creating a C# (.NET BETA2) Windows application (NOT a Web application, a
Windows application), and I would like to select multiple rows
inside of a DataGridView. Is this possible and if so how?
Thanks,
David
|
by: David |
last post by:
I was looking at this article, but it doesn't work for me.
http://msdn2.microsoft.com/en-us/library/system.windows.forms.datagridview.multiselect.aspx
I can not select multiple rows in my datagridview. Any ideas?
Thanks,
David
|
by: Bob Loveshade |
last post by:
I am looking for an example that shows how to select and highlight
multiple rows in a DataGrid.
My DataGrid is part of a Web User Control which is contained in an ASPX
page.
I haven't been able to find any examples which demonstrate how to do
this.
My Code:
|
by: Hellstenius |
last post by:
Hello everybody!
I need your help with getting the result from a select statement in ms sql, that returns 8 rows, with 4 different values. What I want to have is one row with 9 values.
This is how the result looks today:
Region------------Year-------Quantity
1-Stockholm----2003---------59
2-Gothenburg---2003---------22
1-Stockholm----2004---------33
2-Gothenburg---2004---------44 etc...
|
by: Ettenurb |
last post by:
I was hoping someone has come across this and came up with a solution.
We have upgraded our custom software to us Infragistics UltraWinGrid
2006 CLR 2.0. The code below worked with a previous release of the
UltraWinGrid.
When the user clicks on the 'Select All' button this code is selecting
all rows in the grid. The grid has band(0) and band(1) (Parent and
multiple children)
| |
by: arockiasamy |
last post by:
hi,
How to select multiple rows in a table using java script? and that selected rows must be deleted?
i have done for single selection. how it can be for multiple?
come on help me.. i dont want to use list? . only table rows created by html.
kindly help
with regards
sam
|
by: chrisale |
last post by:
Hi All,
I've been racking my brain trying to figure out some sort of Sub-Select
mySQL statement that will create a result with multiple rows of
averaged values over a years time.
What I have is weather data. There is a new record every 5 minutes,
every day. So. What I want to do with one SQL statement is figure out
the Average of those 5 minute records over each day, for every day of
the year.
|
by: khairulfnd |
last post by:
i want to select multiple rows using a checkbox in a gridview in which it will be posted in another gridview
|
by: Michael |
last post by:
It seems that a gridview allows us to delete only a single row at a time.
How to extend this functionality to select multiple rows and delete all of
the selected rows in a single stroke? just like what hotmail web UI is doing
now (having the option of selecting multiple rows (using the checkbox
provided) and perform a set of operations on them)
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |