Dotaz: where podminka, pouze pokud se podari join

[1john2]

dobry den, mam tento select:

SELECT USERS.id, USERS.username, USERS.password, USERS.id_about, USERS.last_login, USERS.valid, COMMENTS.value AS about
                        FROM USERS
                        LEFT JOIN COMMENTS ON `COMMENTS`.`id_owner` = `USERS`.`id_about`
                        WHERE  USERS.id = $podminka AND
                        COMMENTS.id_comment_type =  ( SELECT id FROM SETTINGS WHERE SETTINGS.value = 'comment_about_user') AND
                        `COMMENTS`.`id_owner` = `COMMENTS`.`id_subject`
                        LIMIT 1 

kde jediny promenlivy parametr je $podminka(meni se od 1 dal).

rad bych dosahl toho, ze dalsi podminky ve where se uplatni pouze kdyz se povede join.

potreboval bych, aby se druha a treti cast podminky where uplatnila, jen kdyz se povede left join, napada nekoho, jak toho docilit?

diky za napady

---

---

Odpovědi

[podlesh]

Pokud se left join "nepovede", pak vsechny sloupce z tabulky "napravo" jsou NULL

SELECT USERS.id, USERS.username, USERS.password, USERS.id_about, USERS.last_login, USERS.valid, COMMENTS.value AS about
                        FROM USERS
                        LEFT JOIN COMMENTS ON `COMMENTS`.`id_owner` = `USERS`.`id_about`
                        WHERE  USERS.id = $podminka AND 
                        ( COMMENTS.`id_owner` IS NULL OR (
                        COMMENTS.id_comment_type =  ( SELECT id FROM SETTINGS WHERE SETTINGS.value = 'comment_about_user') AND
                        `COMMENTS`.`id_owner` = `COMMENTS`.`id_subject`
                        ))
                        LIMIT 1 

[1john2]

diky neco takoveho me napadlo, ale nevedel sem, jak to zapsat...

[Tarmaq]

A co neco takoveho? Tzn. zahrnout ty podminky do left joinu

SELECT
  u.id
, u.username
, u.password
, u.id_about
, u.last_login
, u.valid
, c.value AS about
FROM users u
LEFT JOIN comments c ON c.id_owner = u.id_about AND c.id_owner = c.id_subject
LEFT JOIN settings s ON s.id = c.id_comment_type AND s.value = 'comment_about_user'
WHERE u.id = $podminka
LIMIT 1