0

I'm using Access for a cookbook database, an excercise for Uni.

I'm trying to make a query for a many-to-many relationship.

I have ricette and ingredienti tables, and a junction table named ricetta_ingrediente. Now I should make a query that retrieves each ricette associated with ingredienti used.

EDIT: Part of the question got deleted, I need to retrieve the recipe's ingredients of the one with the most ingredients used, it's the result I have to obtain.

Every single try got me either a syntax error or an empty result - how can I achieve this query?

MORE INFOS

The relationship schema

[1

I've tried to implement this suggestion, failing at it, how should it be?

Also here's my try and Access error :

[3]

K3nzie
  • 445
  • 1
  • 7
  • 20
  • Could you privde some sample data and expect result? – D-Shih May 20 '18 at 15:27
  • Show us your schema. And the query(s) you wrote. And the error message. – nicomp May 20 '18 at 15:29
  • @D-Shih everything I'm working with is in italian, is that ok? I avoided pasting stuff for that reason... – K3nzie May 20 '18 at 15:29
  • @nicomp added my query and the access error... – K3nzie May 20 '18 at 16:04
  • Did you type this SQL in SQLView? I expect it would look quite different if you use the DesignView instead. You should post SQL as text, not an image. Also, the English translation of error. And sample data as already requested. – June7 May 20 '18 at 17:01

2 Answers2

1

Use the query builder DesignView to assist in building SQL statement. Result should look like:

SELECT ricette.nome, ingredienti.nome
FROM ingredienti 
RIGHT JOIN (ricette RIGHT JOIN ricetta_ingrediente 
            ON ricette.ID = ricetta_ingrediente.id_ricetta) 
ON ingredienti.ID = ricetta_ingrediente.id_ingrediente;

To retrieve recipe with the most ingredients as well as the ingredients, like:

SELECT TOP 1 ricette.nome, ingredienti.nome
FROM (SELECT id_ricetta, Count([id_ingrediente]) AS CountIng
      FROM ricetta_ingrediente GROUP BY id_ricetta) AS Q1
RIGHT JOIN (ricette RIGHT JOIN (ingredienti RIGHT JOIN ricetta_ingrediente 
                                ON ingredienti.ID = ricetta_ingrediente.id_ingrediente) 
            ON ricette.ID = ricetta_ingrediente.id_ricetta) 
ON Q1.id_ricetta = ricetta_ingrediente.id_ricetta
ORDER BY Q1.CountIng DESC;

This will not resolve ties. All recipes with the number of ingredients matching the TOP 1 count will return. How should the query know you want only 1 and which one?

June7
  • 19,874
  • 8
  • 24
  • 34
  • Thanks for the answer, I don't know why part of my question was deleted, I also need to retrieve the receipt with **most** ingredients and its ingredients (so a count I guess?), how can I do that? This join just retrieves all the data. – K3nzie May 20 '18 at 18:45
  • I edited your question to try and make it as brief and clear as possible. You can edit again. And correct translation would be `recipe`, not `receipt`. – June7 May 20 '18 at 18:54
  • Oh alright :) got it, thanks, sorry for the mistake. I've added the missing part. – K3nzie May 20 '18 at 18:55
  • See revised answer. Since ricetta_ingrediente is a 'junction' table, not a 'pivot' table, also corrected that in your question. – June7 May 20 '18 at 19:50
0

Your query is fine. You just need parentheses, because this is MS Access.

I would also use table aliases:

SELECT r.nome, i.nome
FROM (ricette as r INNER JOIN
      ricetta_ingrediente as ri
      ON r.ID = ri.id_ricetta
     ) INNER JOIN
     ingredienti as i
     ON i.ID = ri.id_ingrediente;

EDIT:

For the revised question:

SELECT TOP (1) r.nome
FROM (ricette as r INNER JOIN
      ricetta_ingrediente as ri
      ON r.ID = ri.id_ricetta
     ) INNER JOIN
     ingredienti as i
     ON i.ID = ri.id_ingrediente
GROUP BY r.nome
ORDER BY COUNT(*) DESC;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the answer, I don't know why part of my question was deleted, I also need to retrieve the receipt with **most** ingredients (so a count I guess?), how can I do that? This inner join just retrieves all the data. – K3nzie May 20 '18 at 18:44