SQL Query Through an Intermediate Table

SQL query through an intermediate table

This is called relational division. A variety of techniques are discussed here.

One alternative not yet given is the double NOT EXISTS

SELECT r.id, r.name
FROM Recipes r
WHERE NOT EXISTS (SELECT * FROM Ingredients i
                  WHERE name IN ('chocolate', 'cream')
                  AND NOT EXISTS
                      (SELECT * FROM RecipeIngredients ri
                       WHERE ri.recipe_id = r.id
                       AND ri.ingredient_id = i.id))

Get information from another table using intermediate table

You only need to add an additional JOIN, like this:

SELECT b.book_id, b.book_name, a.author_id, a.author_name
FROM books b
JOIN books_has_author ba ON ba.books_book_id = b.book_id
JOIN author a ON ba.author_author_id = a.author_id
WHERE a.author_id = xxx

Get all values from intermediate table

You need direct lookup for to get the list of entries, and backward lookup for to get categories list for each entry. Backward lookup must use another tables copies.

SELECT e.entry_text,
       GROUP_CONCAT(c2.category_name) belongs_to
FROM categories c1
JOIN categories2entries ce1 ON c1.category_id = ce1.category_id
JOIN entries e ON ce1.entry_id = e.entry_id
JOIN categories2entries ce2 ON ce2.entry_id = e.entry_id
JOIN categories c2 ON c2.category_id = ce2.category_id
WHERE c1.category_name = 'Category Name'
GROUP BY e.entry_text

If you need the same for more than one category (maybe even all) then

SELECT c1.category_name,
       e.entry_text,
       GROUP_CONCAT(c2.category_name) belongs_to
FROM categories c1
JOIN categories2entries ce1 ON c1.category_id = ce1.category_id
JOIN entries e ON ce1.entry_id = e.entry_id
JOIN categories2entries ce2 ON ce2.entry_id = e.entry_id
JOIN categories c2 ON c2.category_id = ce2.category_id
  /* WHERE c1.category_name IN ({Category Names list}) */
GROUP BY c1.category_name,
         e.entry_text

Save the intermediate result of SQL query

You can use WITH like below:

WITH resultTable as ( select * from order_table left join customer_table on order_table.id = customer_table.id )

select count(*) from resultTable

How to query across three tables joined by an intermediate table?

select c.*, p.* 
from categories c
inner join PROJECT_CATEGORY pc on pc.category_id = c.id
inner join projects p on pc.project_id = p.id

MySQL - Relational/intermediary tables and JOIN queries?

how do I write an SQL query to to find all recipes with, say, bananas in them?

You can do:

select distinct r.id, r.name
from recipe r
join recipe_ingredient ri on ri.id_recipe = r.id
join ingredient i on i.id = ri.id_ingredient
where i.name = 'banana'

Second, why would I have this third relational table if I can find the same information using JOIN queries without the third tables creation?

Since a recipe can have many ingredients, and an ingredient can be related to many recipies the relationship between those two tables is not 1:n but n:m. Therefore, you need an intermediate table, as shown below:

create table recipe (
  id int primary key not null,
  name varchar(20)
);

create table ingredient (
  id int primary key not null,
  name varchar(20)
);

create table recipe_ingredient (
  id int primary key not null,
  id_recipe int not null,
  id_ingredient int not null,
  quantity double not null,
  foreign key fk1 (id_recipe) references recipe (id),
  foreign key fk2 (id_ingredient) references ingredient (id)
);

If an ingredient showed up in a single recipe always, the structure would be simpler, as you seem to think. This simpler structure would probably look like:

create table recipe (
  id int primary key not null,
  name varchar(20)
);

create table ingredient (
  id int primary key not null,
  name varchar(20),
  id_recipe int not null,
  foreign key fk3 (id_recipe) references recipe (id) 
);

A model like this one is not really practical in this case. You would end up having the same ingredient multiple times. For example, if a cake uses "flour" and bread uses "flour", then "flour" would end up twice in the ingredients table. Not a great idea.

MySql Statement to Select Based On a Column in Intermediate Table

What you are doing right now is you are producing catersian product from tables: employees and jurisdictions. The proper syntax of joins is to explicit define the type of join between two tables.

SELECT  a.*, c.*
FROM    employees a
        INNER JOIN user_jurisdiction b
            ON a.userID = b.userID
        INNER JOIN jurisdictions c
            ON b.jurID = c.jurID
WHERE   c.jurisdiction = 'Texas'

• SQLFiddle Demo

OUTPUT of the current query

╔════════╦═══════════╦══════════╦═══════╦══════════════╗
║ USERID ║ FIRSTNAME ║ LASTNAME ║ JURID ║ JURISDICTION ║
╠════════╬═══════════╬══════════╬═══════╬══════════════╣
║      6 ║ John      ║ Doe      ║     2 ║ Texas        ║
║     11 ║ lisa      ║ lopez    ║     2 ║ Texas        ║
╚════════╩═══════════╩══════════╩═══════╩══════════════╝

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins

---

Related Topics