Newbie Question – Point Me To Right Direction

[iyog]

Found this software VERY 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' row and 'birthdate' row and few others rows.

full_name row have 7 records:

John

Bill

Michael

Gordon

George

Arnold

Mark

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

phone row have 5 records:

568456456

111223346

987665456

879562540

564056658

I need to get into single data table for easy editing is full_name row (from table1) and phone row (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 row 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!

[peterlaursen]

You will need to execute a SELECT … JOIN statement. Refer MySQL documentation: http://dev.mysql.com….1/en/join.html

The SQLyog Query Builder will help you to generate such. Read about it in the program help (help .. help menu).

In this simple case you can also write and execute a “JOIN in the WHERE clause” (what you may find simpler). It would be something like (I abbreviated your column names)

Code:

SELECT table1.name, table2.phone FROM table1, table2 WHERE table1.id = table2.id;

(I assume here that you have already selected the database – if not you can use 'fully qualified table name's like “database_name.table1.name” etc.)

Once the statement has been executed a RESULT tab will open. From here you can edit data after changing the dropdown from 'read only' to one of the tables.

[iyog]

Wow, very good! This is exactly what I wanted!

Now, can you tell me what is the difference between Query and View table aproach? Can I get the same with Objects/Views/Create View… ?

And one additional question – is it possible to somehow format my cells or records there in Results table? Example: to sort records with different colors according to ID 'relationship'.

[peterlaursen]

1)

You may create a VIEW. Just

Code:

CREATE VIEW my_view AS SELECT {your current SELECT statement goes here}

.. and the he VIEW (or the Query if you like) will open in the DATA tab directly, so you will not need to write the queery every time.

Or you can add the query as a 'favorite'.

2)

There are no formatting option like this. For better overviey you may add an ORDER BY clause to the query. It is actually a good idea to do as there is no guarantee in what order the server will retrun data if you don't. It could be

Code:

SELECT table1.id, table1.name, table2.phone FROM table1, table2 WHERE table1.id = table2.id ORDER BY table1.id;

(use ASC or DESC keyword to sort ascending or descending if you want).

[Author]

Posts