full join two tables using a third?

KemarRoach — Mon, 22 Nov 2021 07:11:52 GMT

I asked a similar question two days ago, but after trying to find out how to do what I wanted to do for about three hours yesterday, I stopped and decided to ask for help, because I really don't know why it doesn't work.

I am trying to join two tables using a bridge table, which is working fine for the most part. I have my first table, containing meals (table gericht), and their ID, then I have my second table (table allergen), containing allergens and their code. Now I have my bridge table (table gericht_hat_allergen gha), containing meal IDs and allergen codes.

Not all meals from gericht have allergens, and not all allergens from allergen are used

When I want to join the tables to simply display every meal that has any allergens and the corresponding allergens, it's working great. Same goes for selecting every meal, even those who don't have any allergens, and the reverse, so showing the full list of allergens and the meals that have allergens.

Now, I want to join my tables in a way that allows me to see ALL meals and ALL allergens, even if they don't have any 'partners', so, like this

meal1 | allergen 4<NULL> | allergen 5meal3 | <NULL>

For that, I was trying to use full joins. I thought this would work:

SELECT gericht.id as gericht, allergen.name as allergen
FROM gericht_hat_allergen gha
RIGHT JOIN gerichtON gha.gericht_id = gericht.id
FULL JOIN allergenON gha.code = allergen.code

However, PHPStorm (which I am using) says that it can't resolve 'allergen' in the first row, and I don't know why. I have already tried reading up on full joins, but to no avail.I am fairly new to SQL (started two days ago), so chances are i am just not seeing a very simple mistake, so any help is appreciated!

topic full join two tables using a third? in The Watercooler

full join two tables using a third?