नीचे दी गई क्वेरी तालिकाओं और अभिलेखों के साथ एक श्रेणीबद्ध संबंध बनाती है:

program -> accreditation_standard_group -> accreditation_standard -> learning_event

निम्नलिखित तालिकाओं की संरचना के आधार पर:

Table program:
        +------------+----------------+
        | program_pk |  program_name  |         
        +------------+----------------+
        
Table accreditation_standard_group: 
        +---------------------------------+-------------------------------+------------+
        | accreditation_standard_group_pk |  accreditation_standard_group | program_fk |            
        +---------------------------------+-------------------------------+------------+
        
Table accreditation_standard:     

        +---------------------------+------------------------+---------------------------------+
        | accreditation_standard_pk | accreditation_standard | accreditation_standard_group_fk |          
        +---------------------------+------------------------+---------------------------------+
    
Table learning_event:     
        +-------------------+----------------------+---------------------------+
        | learning_event_pk |  learning_event_name | accreditation_standard_fk |            
        +-------------------+----------------------+---------------------------+
    

मुझे अब इसे बदलने की आवश्यकता है ताकि तालिका learning_event में accreditation_standard_fk के संदर्भ का उपयोग करने के बजाय, यह एक से कई संबंधों के लिए लुकअप तालिका का उपयोग करता है।

तो लुकअप टेबल learning_event_accreditation_standard_lookup है:

    +-------------------------------------------------+-------------------+---------------------------+
    | learning_event_accreditation_standard_lookup_pk | learning_event_fk | accreditation_standard_fk |          
    +-------------------------------------------------+-------------------+---------------------------+



   SELECT CONCAT('program:', program_pk) AS global_id,
           program_name AS name,
           NULL AS parent_global_id
    FROM program
    UNION ALL
    SELECT CONCAT('accreditation_standard_group:', accreditation_standard_group_pk) AS global_id,
           accreditation_standard_group AS name,
           CONCAT('program:', program_fk) AS parent_global_id
    FROM accreditation_standard_group 
    UNION ALL
    SELECT 
           CONCAT('accreditation_standard_group:', accreditation_standard_group_fk, ',accreditation_standard:', accreditation_standard) AS global_id,
           accreditation_standard AS name,
           CONCAT('accreditation_standard_group:', accreditation_standard_group_fk) AS parent_global_id
    FROM accreditation_standard
    UNION ALL
    SELECT 
           CONCAT('accreditation_standard_group:', accreditation_standard_group_fk, ',accreditation_standard:', accreditation_standard, ',learning_event:', learning_event_name) AS global_id,
           learning_event_name AS name,
           CONCAT('accreditation_standard_group:', accreditation_standard_group_fk, ',accreditation_standard:', accreditation_standard) AS parent_global_id
    FROM learning_event le
    INNER JOIN accreditation_standard ass ON ass.accreditation_standard_pk = le.accreditation_standard_fk
    INNER JOIN accreditation_standard_group asg ON ass.accreditation_standard_group_fk = asg.accreditation_standard_group_pk

मैंने निम्नलिखित की कोशिश की है लेकिन काम नहीं कर रहा है

फ़ील्ड सूची में 'कॉलम 'learning_event_name' अस्पष्ट है'

 SELECT 
           CONCAT('accreditation_standard_group:', accreditation_standard_group_fk, ',accreditation_standard:', accreditation_standard, ',learning_event:', learning_event_name) AS global_id,
           learning_event_name AS name,
           CONCAT('accreditation_standard_group:', accreditation_standard_group_fk, ',accreditation_standard:', accreditation_standard) AS parent_global_id
    FROM learning_event le
    INNER JOIN accreditation_standard ass ON ass.accreditation_standard_pk = learning_event_accreditation_standard_lookup.accreditation_standard_fk
    INNER JOIN learning_event le2 ON le2.learning_event_pk = learning_event_accreditation_standard_lookup.learning_event_fk
    INNER JOIN accreditation_standard_group asg ON ass.accreditation_standard_group_fk = asg.accreditation_standard_group_pk

देखें db-fiddle

एक काम करने वाला बेला काम अच्छा हो ...

0
IlludiumPu36 12 जुलाई 2021, 10:58

3 जवाब

सबसे बढ़िया उत्तर

यह एसक्यूएल चलता है - आपको अपने आंतरिक जुड़ने के क्रम में समस्याएं थीं:


   SELECT CONCAT('program:', p.program_pk) AS global_id,
           p.program_name AS name,
           NULL AS parent_global_id
    FROM program p
    UNION ALL
    SELECT CONCAT('accreditation_standard_group:', asg.accreditation_standard_group_pk) AS global_id,
           asg.accreditation_standard_group AS name,
           CONCAT('program:', asg.program_fk) AS parent_global_id
    FROM accreditation_standard_group asg
    UNION ALL
    SELECT 
           CONCAT('accreditation_standard_group:', acs.accreditation_standard_group_fk, ',accreditation_standard:', acs.accreditation_standard) AS global_id,
           acs.accreditation_standard AS name,
           CONCAT('accreditation_standard_group:', acs.accreditation_standard_group_fk) AS parent_global_id
    FROM accreditation_standard acs
    UNION ALL
    SELECT 
           CONCAT('accreditation_standard_group:', ass.accreditation_standard_group_fk, ',accreditation_standard:', ass.accreditation_standard, ',learning_event:', le.learning_event_name) AS global_id,
           le.learning_event_name AS name,
           CONCAT('accreditation_standard_group:', ass.accreditation_standard_group_fk, ',accreditation_standard:', ass.accreditation_standard) AS parent_global_id
    FROM learning_event le
    INNER JOIN learning_event_accreditation_standard_lookup lup ON le.learning_event_pk = lup.learning_event_fk
    INNER JOIN accreditation_standard ass ON ass.accreditation_standard_pk = lup.accreditation_standard_fk
    INNER JOIN accreditation_standard_group asg2 ON ass.accreditation_standard_group_fk = asg2.accreditation_standard_group_pk
1
David Glance 12 जुलाई 2021, 12:08

सभी कॉलम संदर्भों को योग्य बनाएं। जब भी आप प्रश्न लिखें, तो ऐसा करने की आदत डालें, और आपको इस तरह की समस्या नहीं होगी।

टेबल उपनाम यहां आपके मित्र हैं। मै सुझाव देता हूँ:

SELECT CONCAT('program:', p.program_pk) AS global_id,
       p.program_name AS name,
       NULL AS parent_global_id
FROM program p
UNION ALL
SELECT CONCAT('accreditation_standard_group:', asg.accreditation_standard_group_pk) AS global_id,
       asg.accreditation_standard_group AS name,
       CONCAT('program:', asg.program_fk) AS parent_global_id
FROM accreditation_standard_group asg
UNION ALL
SELECT CONCAT('accreditation_standard_group:', acs.accreditation_standard_group_fk, ',accreditation_standard:', acs.accreditation_standard) AS global_id,
       acs.accreditation_standard AS name,
       CONCAT('accreditation_standard_group:', acs.accreditation_standard_group_fk) AS parent_global_id
FROM accreditation_standard acs
UNION ALL
SELECT CONCAT('accreditation_standard_group:', ass.accreditation_standard_group_fk, ',accreditation_standard:', ass.accreditation_standard, ',learning_event:', learning_event_name) AS global_id,
       le.learning_event_name AS name,
       CONCAT('accreditation_standard_group:', ass.accreditation_standard_group_fk, ',accreditation_standard:', ass.accreditation_standard) AS parent_global_id
FROM learning_event le JOIN
     accreditation_standard ass
     ON ass.accreditation_standard_pk = le.accreditation_standard_fk JOIN
     accreditation_standard_group asg
     ON ass.accreditation_standard_group_fk = asg.accreditation_standard_group_pk;
2
Gordon Linoff 12 जुलाई 2021, 10:32

आपको क्वेरी लॉजिक में ही त्रुटि है। मैसकल नहीं जानता कि इसे कैसे संभालना है और यह एक त्रुटि उठा रहा है

संदेश आपको बता रहा है कि आपको यह निर्धारित करने की आवश्यकता है कि किस तालिका से learning_event_name मान लिया जाए क्योंकि यह एक से अधिक तालिका में है। बस टेबल नाम को <tablename>.learning_event_name की तरह प्रीपेन्ड करें और क्वेरी इसके मान वापस कर देगी।

1
Lelio Faieta 12 जुलाई 2021, 09:28