CODEWITHSUNDEEP
sqljoin
developi
developi

Reputation: 19

join a same table with different tables

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!

Upvotes: 0

Views: 165

Answers (3)

blo0p3r
blo0p3r

Reputation: 6850

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!

Upvotes: 0

Adam Jones
Adam Jones

Reputation: 2470

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.

Upvotes: 1

rsynnest
rsynnest

Reputation: 169

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.

Upvotes: 2

Related Questions