Relational Databases

[twiggy]

Wonder if you can help me? I been trying to find someone/video that explains how the relationship between tables is made but no one yet has made it clear.

So from what I can guess so far the link between two tables is made by simply naming a field the same as the table name?

Example you can have a table named 'a' and a table named 'b' and in table 'b' I could but 'a_id' and that would automatically create the link between tables?

[JelvinJS7]

It doesn't automatically create a link. But it's best to similar fields in different tables the same thing.
Fir example, the blog tutorial uses 2 tables: posts and comments. In the posts table, the fields are "post_id", "post_"user", "post_title", "post_content", and "post_date". The comments table uses the fields "comment_id", "post_id", "comment_user", "comment_content", and "comment_date".

To display a post and all comments, the query is something like this (entirely off the top of ky head)

SELECT
`posts`.`post_content`,
`posts`.`post_user`,
`posts`.`post_title`,
`posts`.`post_date`,
`comments`.`comment_content`,
`comments`.`comment_user`,
`comments`.`comment_content`,
FROM `comments` WHERE `posts`.`post_id`='{$post_id}'
INNER JOIN `posts`
ON `posts`.`post_id`=`comments`.`post_id`

(I have no clue how accurate the FROM, WHERE, and JOIN parts are.)
What happens is that it takes all the necessary data from both tables, and joins them on the "post_id" column, because they will have the same value on both tables

[twiggy]

Hmm still struggling with this concept .

So from your example, post_id will have the same value in the posts table and the fields table because? I can't see hoe they are linked if they are in different tables

[JelvinJS7]

They aren't linked unless you use a query to join the tables. And that link only applies to that query

[jacek]

Exactly, the tables are not linked at all, you can create the same effect as a link when you select information from the table by using a JOIN. I did a video on this quite early on.

so say you wanted to link the table "left_table" to the table "right_table" and they both had a common column "user_id", you would do that like this

SELECT
    `columns`,
    `you`,
    `need`
FROM `left_table`
INNER JOIN `right_table`
ON `left_table`.`user_id` = `right_table`.`user_id`

you can create the link using any columns, they do not have not be the same.