Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
9.0k views
in Technique[技术] by (71.8m points)

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

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.

enter image description here

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...