CODEWITHSUNDEEP
mysqlforeign-keysleft-joinprimary-key
roberthuttinger
roberthuttinger

Reputation: 1194

Left Join on foreign keys

Perhaps my failing is not knowing the terminology, but I am looking to do perform a JOIN on two tables with another table as the foreign keys.

So the id of an item on table A is used to get the foreign key from table B, which is used to get the textual representation from table C.

TABLE A
+----+------------+
| id | name       |
+----+------------+
| 1  | comment    |
+----+------------+
TABLE B
+-----------+------------------+
| object_id | cat_id_ref       |
+-----------+------------------+
|         1 |                2 |
+-----------+------------------+
|         1 |                3 |
+-----------+------------------+
TABLE C
+---------+----------+
| cat_id  | cat_type |
+---------+----------+
|       1 | Mean     |
|       2 | Nice     |
|       3 | Rude     |
+---------+----------+

So the question is 2 part. what is this 1 step away foreign key lookup called, terminology, and for MySQL is this sub query? Best practice for this type of JOIN

The desired result would be:

+----+------------+------------+
| id | name       | cat_type   |
+----+------------+------------+
| 1  | veg 1      | Nice       |
+----+------------+------------+
| 1  | veg 1      | Rude       |
+----+------------+------------+

Upvotes: 0

Views: 8126

Answers (3)

spencer7593
spencer7593

Reputation: 108480

This looks like a many-to-many relationship. I refer to TABLE B as an "association" table (or, sometimes a "relationship" table, in some cases, this can be called a "membership" table.)

From a database design standpoint, we do "Entity Relationship Modeling". An "entity" is a person, place, thing, concept, or event that can be uniquely identified, is of interest to the business, and we can store information about. And we have "relationships" between those entities. When we have a relationship identified, we ask the right questions, to find out how many of one entity are related to another.

In your example, it looks like B implements an association, between entities A and C.

  • An "A" can be related to zero, one or more "C".
  • A "C" can be related to zero, one or more "A".

(This is where having a suitable name to identify an entity can make the model more understandable.)

To resolve the many-to-many, we implement a third table, which we could name "A_C" or "C_A", or it could have a totally different name. (For example, the association between "COMMITTEE" and "PERSON" might be called "MEMBERSHIP").

The new table, in your example, named "B"

  • A "B" is related to exactly one "A"
  • A "B" is related to exactly one "C"
  • An "A" is related to zero, one or more "B"
  • A "C" is related to zero, one or more "B"

The introduction of the "B" table resolves the many-to-many relationship into two "one-to-many" relationships.

(It looks like you already have an understanding of how a foreign key goes on the table on the "many" side of the relationship, and how that refers to the primary key (or unique key) of the entity table on "one" side of the relationship: the value of the foreign key is a copy of the primary key value from the other table.)

As to the second part of your question, here's an example of a query that will return the specified resultset:

SELECT a.id
     , a.name
     , c.veg_type
  FROM A a
  LEFT
  JOIN B b
    ON b.object_id = a.id
  LEFT
  JOIN C c
    ON c.veg_id = b.veg_type_ref

(There are different queries that will return the same resultset; the big differences will be in the handling of rows that are "missing" (for example, a row in table A that has no "matching" row in table B. The other differences are performance, depending on cardinality, selectivity, available indexes, etc.)

These are all JOINs; there's no need to introduce a subquery to get the specified resultset.

Upvotes: 2

basdwarf
basdwarf

Reputation: 432

Basically, just a one - to - many relationship

Can a veg have more than 1 type? This example seems too simple to require the middle table.

select id,name, veg_type
from TableA
inner join TableB on Tablea.id = tableb.object_id
inner join TableC on tableb.veg_id_ref = tablec.id

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95682

It sounds like you're looking for something like this.

select A.*, C.veg_type
from A
inner join B
        on A.id = B.object_id
inner join C
        on C.veg_id = B.veg_id_ref

Upvotes: 1

Related Questions