[IF][CASE]![JOIN] Złączenie z kilku tabel z warunkami Opcje

[vokiel]

Witam. Mam problem ze złączeniem danych z kilku tabel, w zależności od wartości danych w jednej kolumnie.
Dokładniej:
mam 3 tabele:
PRODUCTS
| ID | NAME | .... |

AWARDS
| ID | NAME |...|

OPERATIONS
| ID | .... | ACTION | PRODUCT |

I teraz pojawia się problem bo mamy ogólnie 3 dostępne akcje:
1 - standard
2 - produkt
3 - nagroda

czyli przykładowe rekordy będą wyglądać tak:
OPERATIONS
| 1 | ... | 1 | NULL |
| 2 | ... | 2 | 2 |
| 3 | ... | 3 | 1 |
| 4 | ... | 2 | 1 |
| 5 | ... | 1 | NULL |

W wyniku chciałbym otrzymać:
QUERY
| 1 | ... | 1 | NULL |
| 2 | ... | 2 | AWARD 2 NAME |
| 3 | ... | 3 | PRODUCT 1 NAME |
| 4 | ... | 2 | AWARD 1 NAME |
| 5 | ... | 1 | NULL |

Zatem JOIN chyba odpada, bo nie będzie wiedział z której tabeli dołączać.
Przydałby się IF a może bardziej CASE

skleciłem coś takiego z case, ale nie działa tak jak trzeba, w kolumnie NAME dostaje wszędzie NULL:

[SQL] pobierz, plaintext 
SELECT 
`OPERATIONS`.`ID` , 
`OPERATIONS`.`ACTION` , 
CASE 
WHEN `OPERATIONS`.`ACTION` =2 THEN `AWARDS`.`NAME` 
WHEN `OPERATIONS`.`ACTION` =3 THEN `PRODUCTS`.`NAME` 
END AS `NAME` , 
FROM `OPERATIONS` LEFT OUTER JOIN `AWARDS` ON `OPERATIONS`.`PRODUCT` = `AWARDS`.`ID` 
LEFT OUTER JOIN `PRODUCTS` ON `OPERATIONS`.`PRODUCT` = `PRODUCTS`.`ID` 
 
WHERE `OPERATIONS`.`ID` >10 LIMIT 0, 30
[SQL] pobierz, plaintext 

A może najlepszym rozwiązaniem byłoby dodanie kolumn dla AWARDS i PRODUCTS?

Ten post edytował vokiel 23.07.2008, 13:07:06

[ddiceman]

Pisze z glowy, wiec wymaga to sprawdzenia

[SQL] pobierz, plaintext 
SELECT operations.id, ..., IF(awards.id IS NULL, products.name, award.name) AS name FROM operations LEFT JOIN products ON operations.action = 2 AND operations.product = product.id
    LEFT JOIN awards ON operations.action = 3 AND operations.product = awards.id
WHERE operations.id > 10 LIMIT 0, 30;
[SQL] pobierz, plaintext 

[vokiel]

Działające poprawnie zapytanie (bez uproszczeń):

[SQL] pobierz, plaintext 
SELECT `PROG_POINTS`.`ID` , `PROG_POINTS`.`CODE` , CONCAT( `PROG_USR`.`IMIE` , ' ', `PROG_USR`.`NAZWISKO` ) AS `USR` , `PROG_POINTS`.`POINTS` , `PROG_POINTS`.`ACTION` , `PROG_POINTS`.`DATE_TIME` , CONCAT( `PROG_USR_JB`.`NAME` , ' ', `PROG_USR_JB`.`SURNAME` ) AS `USR_JB` , 
CASE 
WHEN `PROG_POINTS`.`ACTION` =3
THEN `PROG_AWARD_LIST`.`AWARD_NAME` 
WHEN `PROG_POINTS`.`ACTION` =2
THEN `PROG_PRODUCTS`.`NAME` 
END AS `AWARD_NAME` 
FROM `PROG_USR` , `PROG_POINTS` 
LEFT JOIN `PROG_AWARD_LIST` ON ( `PROG_POINTS`.`ACTION` =3 AND `PROG_POINTS`.`PRODUCT_ID` = `PROG_AWARD_LIST`.`ID` ) 
LEFT JOIN `PROG_PRODUCTS` ON ( `PROG_POINTS`.`ACTION` =2 AND `PROG_POINTS`.`PRODUCT_ID` = `PROG_PRODUCTS`.`ID` ) 
LEFT OUTER JOIN `PROG_USR_JB` ON `PROG_POINTS`.`PASSED_BY` = `PROG_USR_JB`.`ID` 
WHERE `PROG_POINTS`.`USR_ID` = `PROG_USR`.`ID` 
ORDER BY `PROG_POINTS`.`DATE_TIME` DESC LIMIT 0, 100
[SQL] pobierz, plaintext 

Musi być CASE zamiast IF, bo IF(awards.id IS NULL, products.name, award.name) skutkuje, że wyświetla zawsze, tylko products.name, zmieniłem na początku na IS NOT NULL, żeby wyświetlał, jak pole nie będzie puste (ale wtedy niezależnie od parametru ACTION wyświetlał własnie tylko "products.name". Zatem skorzystałem z CASE, który okazał się tu idealnym rozwiązaniem.

@ddiceman bardzo pomocny okazał się Twój LEFT JOIN:

[SQL] pobierz, plaintext 
LEFT JOIN products ON operations.action = 2 AND operations.product = product.id
[SQL] pobierz, plaintext 

Dzięki

[osiris]

Wydaje mi sie ze wydajniejszym zapytaniem byloby:

[SQL] pobierz, plaintext 
(
SELECT `PROG_POINTS`.`ID` , `PROG_POINTS`.`CODE` , CONCAT( `PROG_USR`.`IMIE` , ' ', `PROG_USR`.`NAZWISKO` ) AS `USR` , `PROG_POINTS`.`POINTS` , `PROG_POINTS`.`ACTION` , `PROG_POINTS`.`DATE_TIME` AS czas, CONCAT( `PROG_USR_JB`.`NAME` , ' ', `PROG_USR_JB`.`SURNAME` ) AS `USR_JB` , `PROG_AWARD_LIST`.`AWARD_NAME`  AS `AWARD_NAME`
FROM `PROG_USR` INNER JOIN `PROG_POINTS` ON (`PROG_POINTS`.`USR_ID` = `PROG_USR`.`ID` AND `PROG_POINTS`.`ACTION` = 3)
LEFT JOIN `PROG_AWARD_LIST` ON `PROG_POINTS`.`PRODUCT_ID` = `PROG_AWARD_LIST`.`ID`
LEFT JOIN `PROG_USR_JB` ON `PROG_POINTS`.`PASSED_BY` = `PROG_USR_JB`.`ID`
)
UNION ALL
(
SELECT `PROG_POINTS`.`ID` , `PROG_POINTS`.`CODE` , CONCAT( `PROG_USR`.`IMIE` , ' ', `PROG_USR`.`NAZWISKO` ) AS `USR` , `PROG_POINTS`.`POINTS` , `PROG_POINTS`.`ACTION` , `PROG_POINTS`.`DATE_TIME` AS czas , CONCAT( `PROG_USR_JB`.`NAME` , ' ', `PROG_USR_JB`.`SURNAME` ) AS `USR_JB` , CASE WHEN `PROG_POINTS`.`ACTION` = 2 THEN `PROG_PRODUCTS`.`NAME` ELSE NULL END AS `AWARD_NAME`
FROM `PROG_USR` INNER JOIN `PROG_POINTS` ON (`PROG_POINTS`.`USR_ID` = `PROG_USR`.`ID` AND `PROG_POINTS`.`ACTION` <> 3)
LEFT JOIN `PROG_PRODUCTS` ON `PROG_POINTS`.`PRODUCT_ID` = `PROG_PRODUCTS`.`ID`
LEFT JOIN `PROG_USR_JB` ON `PROG_POINTS`.`PASSED_BY` = `PROG_USR_JB`.`ID`
)
ORDER BY czas DESC LIMIT 0, 100
[SQL] pobierz, plaintext 

Wg mnie powyzszy sposob jest wydajnieszy bo wykonujesz jedno zlaczenie mniej, a operacja zlaczenia jest najbardziej obciazajaca.

Pozdrawiam

Ten post edytował osiris 24.07.2008, 13:15:47

[vokiel]

Dzięki za zainteresowanie
Niestety, w Twoim przykładzie, zapytanie wybiera tylko te rekordy, dla których `PROG_POINTS`.`ACTION` = 3

[osiris]

Poprawilem

[vokiel]

No i działa
Trudno mi teraz mierzyć wydajność obu zapytań, bo na stan dzisiajszy w bazie jest jeszcze niewiele rekordów.
Pierwsze wyniki

Twoje zapytanie:
- 0.0035 s
- 0.0021 s
- 0.0020 s

Poprzednie zapytanie:
- 0.0021 s
- 0.0014 s
- 0.0009 s

Zatem na razie trudno wskazać szybsze. Zobacze z czasem jak przybędzie danych.
Wielkie dzięki za pomoc!
pozdrawiam