Hi all
I'm new with MYSQL & have a question
how can I create relationship between tables?:confused:
thanks

You will need to look up information about JOIN's in SQL.

It can be a very confusing subject, read up about it at this link and then go from there.

http://en.wikipedia.org/wiki/Join_(SQL)

You link tables using the primary keys of the records.

For example, if you have a table called 'authors' with a structure like this:


mysql> create table authors (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `authors` VALUES ('','John Grisham'),('','JK Rowling');
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 2

mysql> select * from authors;
+----+--------------+
| id | name |
+----+--------------+
| 1 | John Grisham |
| 2 | JK Rowling |
+----+--------------+
2 rows in set (0.00 sec)


And now you have a table of books that you want to link to the authors; in your book table -- you would create a column called 'author_id', that would link to the primary key from the authors table:


mysql> create table books (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, author_id INT, title VARCHAR(255));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `books` VALUES ('',1,'The Bretheren'), ('',2,'Harry Potter');
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 2

mysql> select * from books;
+----+-----------+---------------+
| id | author_id | title |
+----+-----------+---------------+
| 1 | 1 | The Bretheren |
| 2 | 2 | Harry Potter |
+----+-----------+---------------+
2 rows in set (0.00 sec)


Now you have a relation between the authors and books table (a one to many relationship -- one author can have many books). So to now, get a listing of all books by an author, you need the id of the author and use that to query the books table:


mysql> select books.title from books where books.author_id = 2;
+--------------+
| title |
+--------------+
| Harry Potter |
+--------------+
1 row in set (0.00 sec)


Hope this helps