CODEWITHSUNDEEP
mysqljoingroup-by
local idiot
local idiot

Reputation: 49

mysql group by set id

MYSQL Database:

I have a table of data that I need to put into two tables.The one table contains persons and an animal. Every record is a person and an animal. I am chaning this to a multi table database and want to group by the persons name, and then give the group an id (like a customer id) and then in my other table pass the customer ID to the idcustomer to join the two tables. To simplfy this i dont mind if these newly created ids are in the single table with new column names. I can after the fact export them out and import them.

The question really is, how can I create a group by customer name, give that customer and ID and then use that same id in a column to do the join.

To describe the scheme:

I have taken over a project. The database as of now is one table. In this one table is:

persons name, address, childsname, description of child

What would like it to be at least to start with is:

id_person, person name, childsname, childparent, description of child.

The id of the person and the childsparent should be the same. When I break the table down, and put the child information in the child table, the child will have a parent id.

I still plan on having two tables. But I can export and create the tables, my problem is assiging the parent id to the child column with the current data.

An example of a couple rows would be:

person_name, address, childsname, description

mark twain, 23 st., Buckweat, short smart kid

mark twain, 23 st., Daniel, short kinda smart

Instead i would like to have 2 tables, one for the parents and the other table is their children.

The way this database was setup, if one person has more than one child, there is another row that lists all of the same information.

What I have planned is to have multiple tables with a join.

The original database has no index, or unique identifier.

What I want to do is loop through the records, since there is no unique id, if the customer name is identical, meaning they are listed twice, then they must have more than one child.

In that case, i want to be able to go through the database and assign a id for the parents. and also ad another colum called parentid, which will be the child table.

Upvotes: 0

Views: 1309

Answers (1)

Galz
Galz

Reputation: 6832

To create the table you need you can use a temporary table - to which you will insert all parent names and give them IDs. Then you can update the existing table:

CREATE TABLE name_to_id (
`id` INT(11) AUTO_INCREMENT,
`name` VARCHAR(256),
PRIMARY KEY (`id`));

INSERT INTO name_to_id (name)
SELECT DISTINCT name FROM my_table;

ALTER TABLE my_table
ADD COLUMN id INT(11) FIRST,
ADD COLUMN parent_id INT(11) AFTER childsname;

UPDATE my_table t
JOIN name_to_id n ON t.name = n.name
SET t.id = n.id, t.parent_id = n.id;

To create the parents and children separate tables you can use:

CREATE TABLE parents AS
SELECT id, name, address FROM my_table
GROUP BY id;

CREATE TABLE children AS
SELECT childsname, parent_id, description
FROM my_table;

You would probably want to ALTER those tables later to add a primary keys and other needed indexes.

Upvotes: 2

Related Questions