How to Select table for Join using Case in SQL-CodePudding

i have a table attribute_name in which a column c_type indicate what type of value we have like 1,2,3,4 so that base on that value i decide which table to join . so i select that table first Join (case statment) On (case statment) but i does not work.

SELECT attribute_names.*,attributes_trans_name.*,
    (CASE
     WHEN attribute_names.c_type=1
     THEN attribute_values_text.c_fk_files_id
     WHEN attribute_names.c_type=3
     THEN attribute_values_longtext.c_fk_files_id
     WHEN attribute_names.c_type=8
     THEN attribute_values_file.c_fk_files_id
    END) as file_id
From attributes_trans_name, 
    attribute_names  JOIN 
    (CASE
     WHEN attribute_names.c_type=1
     THEN attribute_values_text 
     WHEN attribute_names.c_type=3
     THEN attribute_values_longtext 
     WHEN attribute_names.c_type=8
     THEN attribute_values_file 
    END)
    ON  
    (CASE
     WHEN attribute_names.c_type=1
     THEN attribute_values_text.c_fk_attribute_names_id 
     WHEN attribute_names.c_type=3
     THEN attribute_values_longtext.c_fk_attribute_names_id 
     WHEN attribute_names.c_type=8
     THEN attribute_values_file.c_fk_attribute_names_id 
    END) = attribute_names.c_id
    
WHERE 
    attribute_names.c_id=attributes_trans_name.c_fk_attribute_names_id

CodePudding user response：

With proper JOIN/LEFT JOIN context, you can do in single query. Left join means I want the record from the left side always, but OPTIONAL if there is a match on the right side. So, I have adjusted your query to reflect. I have also rewritten to use "alias" names for the file names so it is shorter for read and write than bulky long table names.

So, the main table is the attribute_names as that appears to be the basis of all the joins with the C_ID column into each of the others. Notice indentation helps me know / follow what is linked to what, and not just all tables listed in bulk.

Now, by having each of the left-joins in place, it will ALWAYS TRY to link to their respective other tables by the foreign key, but as you know your data, only one of them will really have the piece of information you need. So your CASE construct is simplified down. If = 1, then look at the ATV (alias) table and its column, otherwise AVLT alias if = 3 and finally AVF if = 8

SELECT 
        AN.*,
        ATN.*,
        CASE WHEN AN.c_type = 1
            THEN ATV.c_fk_files_id
            WHEN AN.c_type = 3
            THEN AVLT.c_fk_files_id
            WHEN AN.c_type = 8
            THEN AVF.c_fk_files_id END as file_id
    From 
        attribute_names AN
            JOIN attributes_trans_name ATN
                ON AN.c_id = ATN.c_fk_attribute_names_id
            LEFT JOIN attribute_values_text AVT
                ON AN.c_id = AVT.c_fk_attribute_names_id 
            LEFT JOIN attribute_values_longtext AVLT
                ON AN.c_id = AVLT.c_fk_attribute_names_id 
            LEFT JOIN attribute_values_file AVF
                ON AN.c_id = AVF.c_fk_attribute_names_id