SQL to list orgs with blank/NULL code, serial no., Yes/No flag, and site name.

Chalo step by step samajhte hain 👇

SELECT 
    (@row_number := @row_number + 1) AS serial_number,
    o.organization_code, 
    o.organization_name, 
    o.created_at, 
    CASE 
        WHEN o.is_mch_record = 1 THEN 'Yes' 
        ELSE 'No' 
    END AS is_mch_record,
    s.site_name
FROM organizations o
LEFT JOIN site_master s 
    ON s.school_id = o.id
CROSS JOIN (SELECT @row_number := 0) AS r
WHERE o.organization_code = '' 
   OR o.organization_code IS NULL;

SELECT 
    (@row_number := @row_number + 1) AS serial_number,
    o.organization_code, 
    o.organization_name, 
    o.created_at, 
    CASE 
        WHEN o.is_mch_record = 1 THEN 'Yes' 
        ELSE 'No' 
    END AS is_mch_record,
    s.site_name
FROM organizations o
LEFT JOIN site_master s 
    ON s.school_id = o.id
CROSS JOIN (SELECT @row_number := 0) AS r
WHERE o.organization_code = '' 
   OR o.organization_code IS NULL;

1. CROSS JOIN (SELECT @row_number := 0) AS r

• Yahaan ek variable @row_number banaya gaya hai jiska initial value 0 set kiya gaya hai.
• Iska use serial number (row numbering) generate karne ke liye kiya jaa raha hai.

2. (@row_number := @row_number + 1) AS serial_number

• Har row ke liye @row_number ka value +1 ho jaata hai.
• Matlab ek ek karke rows ko serial number (1,2,3,4…) assign ho jaata hai.

3. o.organization_code, o.organization_name, o.created_at

• Ye organizations table ke columns directly select kiye gaye hain.

4. CASE WHEN o.is_mch_record = 1 THEN 'Yes' ELSE 'No' END AS is_mch_record

• Agar is_mch_record = 1 hai toh output Yes aayega.
• Agar is_mch_record = 0 ya NULL hai toh output No aayega.
• Matlab human readable format mein boolean value dikhayi jaa rahi hai.

5. LEFT JOIN site_master s ON s.school_id = o.id

• organizations table ko site_master table se join kiya gaya hai.
• Condition: site_master.school_id = organizations.id
• LEFT JOIN ka matlab: agar site_master mein matching record nahi mila toh bhi organizations ka record show hoga, bas site_name NULL ho jaayega.

6. WHERE o.organization_code = '' OR o.organization_code IS NULL

• Sirf wahi organizations records aayenge jinke organization_code blank ('') ya NULL hain.
• Matlab yeh query sirf invalid ya incomplete organization_code waale records ko fetch kar rahi hai.

Final Output Example

• Har row ke liye ek serial number
• Organization code, name, created date
• is_mch_record ka Yes/No
• Agar site_master mein mapping hai toh site_name, warna NULL

👉 Simple words mein:
Yeh query un organizations ko list kar rahi hai jinke organization_code blank ya NULL hai, unko ek ek karke serial number de rahi hai, is_mch_record ko readable Yes/No mein convert kar rahi hai, aur unke corresponding site_name (agar mila toh) dikha rahi hai.