CODEWITHSUNDEEP
mysqlsql
Ted
Ted

Reputation: 4166

Fill new table from other tables

I have 3 tables:

  1. questions
  2. tags
  3. questions_tags -> pivot table (many to many relation)

I need to create new table called "tags_languages" and fill it with tag's languages based on question's language (many to many relation) from "questions" and "tags" tables

tables structure:

"questions" table

id     text     language_id
1     What..        1
2     How...        1
3     Is...         2
4     Is...         2
5     Where..       3

"tags" table

id     text
1      London
2      England
3      ball
4      love
5      yellow

"questions_tags" table

id     question_id     tag_id
1          1              1
2          1              2
3          2              1
4          2              2
5          2              3
6          1              3
7          3              2
8          2              4
9          1              4
10         4              3

required table:

"tags_languages": (tag_id & language_id) -> composite primary key

tag_id     language_id
  1             1
  2             1
  2             2
  3             1
  3             2
  4             1

thanks,

Upvotes: 0

Views: 29

Answers (1)

Rick James
Rick James

Reputation: 142218

This generates the output you mention:

SELECT  qt.tag_id, q.language_id
    FROM  questions q
    JOIN  questions_tags qt ON qt.question_id = q.id;

It is "wrong" to put that into a table since you can always reconstruct it on the fly. (Redundant data in a database is a no-no.)

Sticking this in front of the SELECT will create the table:

CREATE TABLE tags_languages (
    PRIMARY KEY(tag_id, language_id) )

"Many-to-many" and "pivot" are not synonyms.

Perhaps your real goal is something beyond "I need to create new table". Would you care to discuss that?

Upvotes: 1

Related Questions