Bir nechta jadval va self join

Ko‘p savollar ikki emas, uch-to‘rt jadvalni talab qiladi. Ba’zan esa jadvalni o‘zi bilan ulash kerak bo‘ladi.

Ketma-ket JOIN

Har bir JOIN natijaga yana bitta jadvalni qo‘shadi. Talaba → guruh → fakultet:

SELECT s.first_name, s.last_name, g.name AS group_name, f.name AS faculty
FROM students s
JOIN study_groups g ON g.id = s.group_id
JOIN faculties f ON f.id = g.faculty_id
LIMIT 8;

Oraliq jadval orqali (M:N) — talaba, fan va baho:

SELECT s.first_name, sub.name AS subject, gr.score
FROM grades gr
JOIN students s ON s.id = gr.student_id
JOIN subjects sub ON sub.id = gr.subject_id
ORDER BY gr.score DESC
LIMIT 8;

Har bir JOIN uchun alohida ON yoziladi. Zanjirni sxema bo‘yicha quring: qaysi jadvaldan boshlasangiz, keyingisi unga ulanadigan bo‘lsin.

Self join — jadvalni o‘zi bilan ulash

company bazasida har bir xodimning rahbari ham employees jadvalining o‘zida turadi (manager_id). Xodim va rahbarini yonma-yon ko‘rish uchun jadvalni ikki marta, ikki xil taxallus bilan olamiz:

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
JOIN employees m ON m.id = e.manager_id
LIMIT 8;

e — xodim sifatida, m — rahbar sifatida. Taxallussiz bu so‘rovni yozib bo‘lmaydi.

Ko‘p jadvalli hisob

To‘rt jadval: buyurtma qatorlari → buyurtmalar → mahsulotlar → toifalar. Yetkazilgan buyurtmalardan har bir toifa tushumi:

SELECT c.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
JOIN categories c ON c.id = p.category_id
WHERE o.status = 'delivered'
GROUP BY c.name
ORDER BY revenue DESC;

Esda qoldiring

• Har bir qo‘shimcha jadval — yana bitta JOIN … ON ….
• Self join: bitta jadval, ikki xil taxallus.
• So‘rov yozishdan oldin sxemada «yo‘l»ni toping.