CODEWITHSUNDEEP
mysqlwordpressjoin
Tompo
Tompo

Reputation: 341

SQL many to many relation

I’m using Wordpress quiz plugin. It has two tables (in fact more, but I’m trying to focus on the issue) – ‘questions’ and ‘questionpots’:

‘questions’: questionID ---- Question ---- potID

‘questionpots’: potID ---- potName

And the default query is: $SQL = 'Select * FROM questions WHERE potID='.$potID;

So by default every question is assigned to a specific pot (‘potID’ in ‘questions’ table) and the relation is ‘one to many’. What I want to do is to assign questions to MORE than just one pot, so that for example the question “When did Rome fell?” would be asked whether someone choose “Rome history” pot OR “Ancient history” pot.

So I added the third table, ‘relations’, which matches questionIDs and potIDs. The problem is I can’t figure out the query to select * from ‘questions’ WHERE potID='.$potID assuming that there’s one or MORE than one question for $potID.

How should I join this tables? Thanks.

Upvotes: 0

Views: 109

Answers (1)

spencer7593
spencer7593

Reputation: 108370

To resolve a many-to-many relationship between two entities, we add a third table, a relationship table, with foreign keys referencing the two entity tables.

A row added to the relationship table represents a (wait for it...) relationship between the two entities.

As an example, assuming tables question and pot both have an id column as the primary key:

CREATE TABLE question_pot
( question_id     INT UNSIGNED NOT NULL COMMENT 'fk ref question' 
, pot_id          INT UNSIGNED NOT NULL COMMENT 'fk ref pot'
, PRIMARY KEY (question_id, pot_id)
, CONSTRAINT FK_question_pot_question 
     FOREIGN KEY (question_id) REFERENCES question(id)
, CONSTRAINT FK_question_pot_pot
     FOREIGN KEY (pot_id) REFERENCES pot(id)
) ENGINE=InnoDB

To add a relationship from question id=444 to two pots pot id=7 and pot id=13:

INSERT INTO question_pot VALUES (444,7), (444,13);

And scrap the pot_id column in the question table. You can save those first...

INSERT INTO question_pot (question_id, pot_id) 
SELECT q.id
     , q.pot_id
  FROM question q
 WHERE q.pot_id IS NOT NULL
;

And then drop the pot_id column from question.

If you've gotten that far, now you just need to perform JOIN operations...

To get all questions in pot id=13, for example:

SELECT q.*
  FROM question q
  JOIN question_pot r
    ON r.question_id = q.id
 WHERE r.pot_id = 13

FOLLOWUP

The query above gives an example of assigning an alias to a table, and using the alias to qualify column references.

Qualifying column references is best practice; it makes the statement easier for someone to read and interpret, without requiring them to lookup the definitions of the tables to figure out which column is coming from which table.

Compare the query above to this:

SELECT *
  FROM question
  JOIN question_pot
    ON question_id = id
 WHERE pot_id = 13

Now, try answering these questions: Is id a reference to a column from question or question_pot. (If this query is in a MySQL stored program (procedure, function, trigger), is the reference to id a reference to a column, or to a procedure variable.)

In some cases, we have to qualify column references to make the reference unambiguous, when the reference can refer to a column in multiple tables, and causes MySQL to throw an ambiguous column reference error.

It's possible to have a query working just fine, and then add a column to a table, e.g. adding id column to the question_pot table, and then the query will start failing with the "ambiguous column" error.

In my way of thinking, it's not acceptable that the addition of a column to a table should cause a "working" query to break. We can prevent that kind of error popping up in the future, simply by explicitly qualifying the column reference, even if it's not required right now.

As far as why we choose to use short aliases like q and r in place of just the table name.

In EXACTLY the same way you've identified the table names in your comment:

wp_ai_quiz_tblquestions    (=question)
wp_ai_quiz_tblquestionpots (=pot)
wp_ai_quiz_question_pot    (=question_pot)

I'm essentially saying the same thing with the table aliases:

wp_ai_quiz_tblquestions     (=q)
wp_ai_quiz_tblquestionpots  (=p)
wp_ai_quiz_question_pot     (=r)

Compare reading this:

SELECT q.*
  FROM wp_ai_quiz_tblquestions q
  JOIN wp_ai_quiz_question_pot r
    ON r.question_id = q.id
 WHERE r.pot_id = 13

To reading this:

SELECT wp_ai_quiz_tblquestions.*
  FROM wp_ai_quiz_tblquestions
  JOIN wp_ai_quiz_question_pot
    ON wp_ai_quiz_question_pot.question_id = wp_ai_quiz_tblquestions.id
 WHERE wp_ai_quiz_question_pot.pot_id = 13

Throw in a few more tables, and also don't line things up like I always line things up, and it requires more effort from the reader to decipher what the statement is doing. In really complicated queries, referencing half a dozen or more tables, I'll add comments above the query, explaining the usage of the aliases.

-- find all questions in a particular pot
-- q = question row to be returned from wp_ai_tblquestion 
-- r = relationship between question and pot to be searched
SELECT q.id
     , q.name
     , ...

Upvotes: 2

Related Questions