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.

Because `CATFOOD_TYPE` is defined in the alias named `TEMP`, you should use `TEMP.CATFOOD_TYPE` in your field list. — Luuk, Feb 04 '21 at 19:57
Sorry, that should be CATFOOD_TYPE, not CARFOOD_TYPE. Updating the question now. — Agent47, Feb 04 '21 at 20:00
You should probably change the `RIGHT JOIN`s to `LEFT JOIN`s...., Please also provide some sample data... — Luuk, Feb 04 '21 at 20:02
[What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?](https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) — Luuk, Feb 04 '21 at 20:05
@AkilaAmarasinghe Can you provide some sample input output. It will be helpful to check. — Srijon Chakraborty, Feb 04 '21 at 20:06

score 1 · Answer 1 · answered Feb 04 '21 at 20:16

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';

score 0 · Answer 2 · answered Feb 04 '21 at 20:00

0

You got a typo, should be CATFOOD_TYPE, not CARFOOD_TYPE

answered Feb 04 '21 at 20:00

Josy Sclei

57
1
10

score 0 · Answer 3 · answered Feb 04 '21 at 20:24

I think you can try this =>

SELECT DISTINCT c.CAT_ID, 
       CAT_TYPE, 
       CAT_COLOR,cb.CUB_ID, 
       cb.CUB_NAME, 
       cf.CATFOOD_TYPE,
       cf.CATFOOD_ID,
       cf.CATFOOD_STATUS,
       cf.SELLER_ID,
     bd.BRAND_ID,bd.FLAVOUR 
FROM CAT c
LEFT JOIN CUBS cb ON c.CAT_ID=cb.CAT_ID
LEFT JOIN CATFOOD cf ON c.CAT_ID=cf.CAT_ID 
LEFT JOIN BRAND bd ON cf.CATFOOD_ID=bd.CATFOOD_ID

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

3 Answers3