Insert into query with 4 tables joint

Question

I have 5 tables :

=>posts

ID

post_author

post_content

post_title

post_status

post_name

=>items

id

title

category

item

=>terms

term_id //the category id

category //items.category

slug

=>term_taxonomy

term_taxonomy_id

term_id //the category id

taxonomy //the category name

=>term_relationships

object_id // posts.ID

term_taxonomy_id

I think the query should be like this :

---

insert into term_relationships (object_id,term_taxonomy_id) select a.ID, b.term_taxonomy_id from posts a, term_taxonomy b, where terms.category = item.category

---

How can I do an insert query with 4 tables joint ?

Answer 1

You need to use JOINs. It's not completely possible to answer your question without knowing how you want to JOIN the tables. I'd suggest looking at this link

Answer 2

OK, you need to something like this:

INSERT INTO term_relationships
([object_id], term_taxonomy)
(SELECT a.[ID], b.term_taxonomy_id
FROM items i
INNER JOIN posts a
ON i.title = p.post_title
INNER JOIN terms t
ON i.category = t.category
INNER JOIN term_taxonomy b
ON b.term_id = i.category

BUT - I do not know if you want INNER JOINs between your tables. You might want OUTER JOINs or a combination of INNER and OUTER JOINs. There's no way for me to know without knowing how you want the data to combine