I have 4 tables and I wish to use a mysql inner join to the users name (table 1) and type of fav(table 4). Inorder to do this, I need to use user_id to get get corresponding fav_id on table 2, then use the fav_id to get type_of_fav_id on table 3, and finally use type_of_fav_id on table 3 to get type_of_fav on table 4. I will appreciate any help.
Table 1
Users
----------
user_id
first_name
last_name
Table 2
user_favorite
--------
user_fav_id
user_id
fav_id
Table 3
favorites
--------
fav_id
type_of_fav_id
Table 4
types_of_fav
--------
type_of_fav_id
type_of_fav
It's pretty straightforward, actually. Just join each of the four tables, linking the primary key of each to its foreign key counterpart in the following table. Then you can reference the fields that you want in your SELECT clause.
SELECT u.first_name, u.last_name, tof.type_of_fav
FROM users u
JOIN user_favorite uf ON u.user_id = uf.user_id
JOIN favorites f ON uf.fav_id = f.fav_id
JOIN types_of_fav tof on f.type_of_fav_id = tof.type_of_fav_id
It's a good idea to set up abbreviations of the table names as I have it here. You don't have to do that, but it's more concise and easier to work with. If you don't do it, you have to reference the full table name, to avoid ambiguity in field references: JOIN user_favorite on users.user_id = user_favorite.user_id, for example. That gets pretty long-winded.
If you don't include the tables when referencing the fields, the parser doesn't know which field you're referencing, since your field names aren't unique in your entire schema. So, you'll get an ambiguous field reference error.