Devart Forums

Posted: **Sat 23 Jul 2011 05:09**

Using dbForge Studio for MySQL 5.0 Express and found this software very useful. But would require some help to get me started with it according to me needs. I am beginner in MySql management but want to learn.

For what I want to use SQLyog is for simple data editing of my MySQL database in grid view (for easy editing excel like manner).
But for that I need to have my data sorted in a best way for easy viewing. The problem is that my important data is in two separate tables. I want to have data from two tables into one single table where will be easy to look and edit in that way.
In which way I must work on to have data from 2 tables visible and well sorted into one single table?

Simple example: I have 'table1_fullname' and 'Table2_nickname'.
In table1_fullname have 'full_name' column and 'birthdate' column and few others columns.

full_name column have 7 records:
John
Bill
Michael
Gordon
George
Arnold
Mark

In table2_nickname have 'nick' column and 'phone' column and few more.

phone column have 5 records:
568456456
111223346
987665456
879562540
564056658

I need to get into single data table for easy editing is full_name column (from table1) and phone column (from table2). So, every phone number need to have proper name showed there.
The exact data look would be:

John 568456456
Bill 111223346
Gordon 987665456
Arnold 879562540
Mark 564056658

Please note that in table1 have ID number added in separate column for every record there and those ID numbers are given also in table2 for every record so suppose according to that ID number proper connection (relationship) can be found between those two data which I need to get.

Thanks in advance for pointing me to right direction for this!

Posted: **Mon 25 Jul 2011 09:01**

For example, we have tables and data like the following:

Code: Select all

CREATE TABLE table1_fullname(
  id INT(11) PRIMARY KEY,
  full_name VARCHAR(255),
  birthdate DATE
);
INSERT INTO table1_fullname VALUES (1, 'John', NULL), (2, 'Bill', NULL), (3, 'Michael', NULL), (4, 'Gordon', NULL), (5, 'George', NULL), (6, 'Arnold', NULL), (7, 'Mark', NULL);

CREATE TABLE table2_nickname(
  id INT(11) PRIMARY KEY,
  id_fullname INT(11), -- ref. to table1_fullname.id
  nick VARCHAR(255),
  phone VARCHAR(255)
);
INSERT INTO table2_nickname VALUES (1, 1, 'jhn', 568456456), (2, 2, 'bil', 111223346), (3, 4, 'gor', 987665456), (4, 6, 'arn', 879562540), (5, 7, 'mrk', 564056658);

Please note, that tables have primary keys to identify row correctly.

To retrieve the needed data for preview and editing, you need to execute the following query:

Code: Select all

SELECT f.id, f.full_name, n.id, n.phone FROM table1_fullname f
JOIN table2_nickname n ON f.id = n.id;

In dbForge Studio for MySQL, v5.0 Data Editor allows editing results of queries with JOIN clauses. That's why you need just to execute the query in a SQL document. After execution you need to choose the table to edit in the corresponding dropdown list on the toolbar of Data Editor (table1_fullname or table2_nickname). By default, the value of this dropdown list will be set as 'read only'.
After you choose the table to edit, the columns for editing will be enabled. You can choose another table to edit any time.

Devart Forums

Point me to right direction

Point me to right direction