join a same table with different tables

Question

I am wondering if anyone can help me, I have these tables:

tbl_search_history(id,fld_user_id,fld_username_fld_make_id,fld_model_id,fld_year_id,fld_category_id)

The fld_make is the name of the make:

tbl_make(id,fld_make)

The fld_model is the name of that model

tbl_model(id,fld_model)

The fld_year is the name of that year

tbl_year(id, fld_year)

The fld_category is the name of that category

tbl_category(id,fld_category)

Now I need to show a table in my page in which I need to have this fields: make, model, year, and category. I also want to know how can I have the make, model, year, and category names (instead of just the reference numbers from the tbl_search_history) in my page? Would you please give me a piece of code and a brief explanation?

Thanks in advance!

Could you please show us your code and tell us what have you tried so far? — consuela, Sep 29 '14 at 23:38

score 2 · Answer 1 · edited May 23 '17 at 12:05

Your question is a bit unclear, but it sounds like a homework problem.
I will give you some pseudocode and references.
First off I would do some reading on JOINS.
If you are asking to get Column Names, see this SO question
I believe what you want to do is join your tables like so:

SELECT table1.desired_field, table2.desired_field2, table3.desired_field3
FROM table1
JOIN table2 ON table1.table2_id = table2.id
JOIN table3 ON table1.table3_id = table3.id

and so on.

score 1 · Answer 2 · answered Sep 29 '14 at 23:47

SELECT 
    tbl_model.fld_make,
    tbl_make.fld_model,
    tbl_year.fld_year,
    tbl_category.fld_category
FROM
    tbl_search_history
    INNER JOIN tbl_make ON tbl_search_history.fld_make_id = tbl_make.id
    INNER JOIN tbl_model ON tbl_search_history.fld_model_id = tbl_model.id
    INNER JOIN tbl_year ON tbl_search_history.fld_year_id = tbl_year.id
    INNER JOIN tbl_category ON tbl_search_history.fld_category_id = tbl_category.id

Assuming that the search history table is the table that links all these other tables together. Also assuming that all of these tables have linked values (ie non-null id's in tbl_search_history) otherwise you'd need to use an OUTER JOIN.

score 0 · Answer 3 · answered Sep 29 '14 at 23:48

What you want to do is to join with the make, model, year, and category tables using the ids you have in the tbl_search_history.

Something of this sort will give you want you want :

SELECT * FROM `tbl_search_history`
INNER JOIN `tbl_make`
    ON tbl_search_history.fld_make_id = tbl_make.id
#keep going for model, year, and category tables using their respective ids.

Hope this helps!

join a same table with different tables

3 Answers3