Count() ve vazební tabulce, 0 pokud není shoda – MySQL – Fórum – Programujte.com

Zdravím,

mám tu problém se složením SQL dotazu. Mám tři tabulky -

server (server_id, name),

offer (offer_id, date, ...)

offer_server (offer_server_id, server_id, offer_id). - vazebni tabulka

Co potřebuji, je počet nabídek (offer) pro každý jednotlivý server, tedy i pro ty které nabídku nemají (není pro daný server záznam v server_offer). Toho jsem schopen docílit dotazem  

SELECT count(sr.server_id) as count_server,s.name 
FROM server s LEFT JOIN offer_server sr 
ON s.server_id = sr.server_id GROUP BY s.server_id

Problém je ten, že v aplikaci mám filtr na datum, který když aplikuji, tak mi dotaz vrátí jen servery s nabídkou, já potřebuji aby to vrátilo všechny a u těchco nemají nabídku bude count_server = 0.

Dotaz s filtrem nyní vypadá takto:

SELECT count(sr.server_id) as count_server,s.name 
FROM server s 
LEFT JOIN offer_server sr ON s.server_id = sr.server_id
WHERE (sr.offer_id IN (SELECT offer_id from offer where YEAR(date) = '$rok'))
GROUP BY s.server_id

Díky za odpovědi.

#1 Lynx
Sloupec offer_server_id je zbytečný. Zbav se ho.

Použij druhý LEFT JOIN místo IN.

UNION ALL nebo HAVING?

problem je ze spajas tabulku s tabulkou a potom ich filtrujes ... skus spojit tabulku s filtrovanou tabulkou ...

(nemam ako vyskusat ale nejak takto)

SELECT count(sr.server_id) as count_server,s.name 
FROM server s 
LEFT JOIN 
  (SELECT server_id, offer_id FROM offer WHERE YEAR(date) = '$rok') sr 
ON s.server_id = sr.server_id

GROUP BY s.server_id

offer_server_id je sice zbytocne z pohladu normalizacie ale ak ho tam nedas tak musis robit index na dva stlpce a programovat so zlozenym PK je trochu narocnejsie

#4 Flowy
Index na ty dva sloupce musíš dělat i tak. Vazební tabulka by se měla obejít bez syntetického PK, protože není nikde potřebný a ani nemá žádný účel. Ani nezabrání zdvojení vazby.

ucel som popisal vyssie ... implementovat a pouzivat ORM ktory spracovava zlozeny PK je narocnejsie ako to spravit s jednym PK hoc bude synteticky ... ak bude mat kazda entita svoje id tak si tym minimalne zjednodusis model

#6 Flowy
Přidání zbytečného sloupce nijak nezjednoduší zpracování, spíš naopak přibude starost o další sloupec a hrozí vznik duplicitních vazeb. Navíc neodpovídá používanému vzoru pro relaci typu M:N.

Podobně ani u relací 1:1 se nedává žádný syntetický primární klíč, ale jako primární se pouze použije cizí klíč. Místo dvou indexů pak stačí jeden.

sprav si patent na slovo 'nijak' ... mam pocit ze si objavil novy vyznam ktory zmeni vsetko co sme doteraz poznali

alebo snad vies dokazat tvoje tvrdenie?

#8 Flowy
Když ne "nijak", tak jinak:

Místo dokazování zbytečnosti by mě zajímal příklad užitečnosti sloupce offer_server_id. K čemu je tedy dobrý? Ve kterých případech má smysl jeho použití? Kdy se bez něj prostě neobejdeš? Ukaž nějaký SQL dotaz, ve kterém by ti jeho nepřítomnost chyběla.

vdaka tomu ze sa dnes nepouziva priamo sql ale rozne ORM a podobne je mozne programovat rychlejsie a s menej chybami ... nevyhoda takychto nadstavieb je ze ich vykon nemusi byt zrovna optimalny a ich pouzitie je v urcitych smeroch obmedzujuce ... proste je jednoduchsie dat kazdej entite id a pracovat s takym ako mu dat zlozeny pk ...

predstav si ze si dal asociacnej tabulke (taka ktora ma vyznam resp obsahuje aj ine asociacne data) zlozeny pk (pretoze to je vsetko co potrebujes na jej identifikaciu) ... a po nejakom case ked uz spravis celu aplikaciu ktora identifikuje entitu na zaklade zlozeneho PK dostanes poziadavku ze ta entita ma 'historiu' teda na zaciatku roka 2011 to je ina entita ako na konci roka (povedzme ze ju niekto zmenil) ale ty chces ukladat hodnotu kazdeho historickeho zaznamu

ak mas zlozeny kluc tak musis pridat do neho novy stlpec a teda TIME ... takze musis prerobit celu aplikaciu a namiesto dvoch hodnot ktorym identifikujes entitu musis posielat tri (tak isto musis donutit uzivatelov tvojej aplikacie aby vyberali verziu)

ak by si mal jednoduche Id ako primarny kluc potom sa zmeni len kod ktory skutocne potrebuje pracovat s casom ... uzivatelia tvojho systemu maju stale ulozene jedno id (ktore je mimochodom aj jednoduchsie na prenos - inak povedane jedno Id obsahuje tu istu informaciu ako zlozeny kluc a teda jeho efektivita je neporovnatelne vyssia)

teoria je pekna a je dobre ju ovladat ale v praxi je nutne rozmyslat nad viacerymi premennymi ktore mozu byt obcas dolezitejsie ako spravne dodrzanie noriem

http://stackoverflow.com/a/63108/2037762

prax dokazuje ze ludia ktory nepoznaju teoriu maju sancu byt uspesnymi a nie vsetci ludia ktory teoriu poznaju su uspesny -> poznat teoriu asi nebude ta najdolezitejsia premenna

predpokladam ze chodis/pracujes v skole a/alebo si este nerobil realny projekt pre realneho klienta?

verim ze existuje pripad kedy je vyhodnejsie pouzit zlozeny PK ale este som taky nenasiel

#10 Flowy
Pokud potřebuji přidat historii, tak to řeším další tabulkou, do které se automaticky ukládají historická data včetně timestampu a identifikace toho, kdo tu změnu provedl. V tabulce ani v samotné aplikaci neměním vůbec nic, pouze přidám další třídy, které budou s těmi historickými daty pracovat. Přesněji řečeno pouze číst, protože do historie není radno zasahovat. Modifikaci tabulek s historií mám zpravidla zablokovánu, je to důležité pro případné důkazní řízení - aby ani šéf oddělení se v tom nemohl hrabat, ale mohl pouze číst.

V takové tabulce s historií již mám syntetický primární klíč, protože k jednomu záznamu může být více změn - dokonce i se stejným timestampem.

Zajímalo by mě, jak řešíš ochranu historických záznamů, pokud je máš ve stejné tabulce jako živá data. Také musí být zajímavé SQL dotazy nad živými daty, když musíš neustále odfiltrovávat historii.

Složený PK se kupodivu používá poměrně často. Dá to sice více práce při normalizaci, ale u velkých projektů se to dlouhodobě vyplatí. Typickým použitím složeného PK je právě vazební tabulka M:N, ve které stačí spojit oba cizí klíče. Další syntetický klíč je již nežádoucí, protože by neměl žádnou funkci a pouze by zdržoval.