sql - Join four tables together when only two tables are connected to the junction table

Question

I have four tables created and I need join all the four tables and get the result. Following is the table structure. The CAT table is the junction table here and from it, it has one to many relations to CATFOOD and CUBS tables. From the CATFOOD table, there's another one to many relationship to the table BRAND.

I tried the following query to first join the BRAND and CATFOOD tables and next join the result of that with CAT table. But it didn't work. Please note that I also join the table CUBS with the table CAT too. Here's what I tried,

SELECT CAT.CAT_ID, 
           CAT_TYPE, 
           CAT_COLOR,CUBS.CUB_ID, 
           CUBS.CUB_NAME, 
           CATFOOD.CATFOOD_TYPE,
           CATFOOD.CATFOOD_ID,
           CATFOOD.CATFOOD_STATUS,
           CATFOOD.SELLER_ID,
           BRAND.BRAND_ID,
           BRAND.FLAVOUR
FROM CAT RIGHT JOIN CUBS
            ON CAT.CAT_ID = CUBS.CAT_ID
            RIGHT JOIN (
                SELECT CATFOOD.CATFOOD_ID,
                            CAT_ID,
                            CATFOOD_TYPE,
                            SELLER_ID,
                            CATFOOD_STATUS,
                       BRAND.BRAND_ID,
                            FLAVOUR
                FROM CATFOOD RIGHT JOIN BRAND
            ON CATFOOD.CATFOOD_ID = BRAND.CATFOOD_ID) 
            AS TEMP ON CAT.CAT_ID = TEMP.CAT_ID
WHERE CAT.CAT_ID = 'some_id_in_the_db';

When I execute this, I get the following error.

Unknown column 'CATFOOD.CATFOOD_TYPE' in 'field list'

When I remove the columns that the error message mention from the select statement, it doesn't give me any errors but returns an empty result.

What I simply want to achieve is get all the details related to a provided CAT_ID so in the java level, I can construct the response appropriately.

Please if anyone know how to achieve this, I appreciate it very much. Thanks in advance.

Update: previously, CATFOOD_TYPE field was mistakenly mentioned as CARFOOD_TYPE in the question. It is corrected now.

question from:https://stackoverflow.com/questions/66052906/join-four-tables-together-when-only-two-tables-are-connected-to-the-junction-tab

Answer 1

You are joining a subquery which contains the column CATFOOD.CATFOOD_TYPE, but this column is not visible to the outer query by that alias/name.
You should alias the subquery, say t and refer to the column as t.CATFOOD_TYPE.

But there is no need to complicate the requirement.
You can join the table cat with all the other tables with LEFT joins:

SELECT c.cat_id, c.cat_type, c.cat_color,
       cu. cub_name,
       f.catfood_type, f.catfood_id, f.catfood_status,
       b.brand_id, b.flavour
FROM cat c
LEFT JOIN cubs cu ON cu.cat_id = c.cat_id
LEFT JOIN catfood f ON f.cat_id = c.cat_id
LEFT JOIN brand b ON b.catfood_id = f.catfood_id
WHERE c.cat_id = 'some_id_in_the_db';