Count dve tabele po kljucu iz trece...

[salebab]

Ceo dan se lomim oko ovoga, presao sam ceo net uzduz i popreko i nista korisno nisam nasao.

Dakle, imam 4 tabele:

grupa(id, naziv)
delatnost(id, grupa_id, naziv)
placeni_oglas(id, i 10 nebitnih kolona)
besplatni_oglas(id, i 5 nebitnih kolona)

Zelim da izlistam sve delatnosti u odredjenoj grupi i za svaku delatnost zbir placenih i besplatnih oglasa.
Na primer:
Usluge (8)
Prodaja (10)

Po inerciji sam probao ovaj sql upit, i vratio mi je pogresni total, cini mi se duplirani.

SELECT d.*, COUNT(po.id)+COUNT(bo.id) as total
FROM delatnost as d
LEFT JOIN (placeni_oglas as po, besplatni_oglas as bo)
    ON (po.delatnost_id = d.id AND bo.delatnost_id = d.id)
WHERE d.grupa_id = 1
GROUP BY d.id

Cudi me da ne radi, jer radi kada se joinuje jedna tabela.
Jel imate mozda neki predlog kako ovo da resim?

Pozdrav!

[DejanVesic]

Možda ovako:

Select grupa_id, Delatnost_ID, DelatnostNaziv, Count(*) From
(
Select del.grupa_id, del.id as Delatnost_ID, del.naziv as DelatnostNaziv, Bo.id as OglasID
From Delatnost Del, besplatni_oglas Bo
Where Bo.id = del.id 
Union All
Select del.grupa_id, del.id as Delatnost_ID, del.naziv as DelatnostNaziv, po.id as OglasID
From Delatnost Del, placeni_oglas Po
Where po.id = del.id 
)
Group By grupa_id, Delatnost_ID, DelatnostNaziv

U zavisnosti od izabrane baze, možda ćeš subquery morati da krkneš pod neki pogled (recimo da T-SQL ne dozvoljava UNION u podupitu, dok Oracle nema ništa protiv).

[salebab]

Hvala na odgovoru, u pitanju je MySQL baza.
Probao sam taj upit, izmenjen za mysql, ne radi...

[DejanVesic]

Ne radi kako? Sintaksno ne radi?

Ako te ne mrzi, okači tabele (strukturu) pa da ja probam lokalno.

[salebab]

Izvinjavam se sto kasnim par dana za odgovorom. Da, sintaksno, koliko mi se cini mySQL bas i ne voli te ugnjezdene upite, mada iskreno, nikad ih nisam koristio...

Evo su tabele i podaci:

--
-- Table structure for table `besplatni_oglas`
--

CREATE TABLE `besplatni_oglas` (
  `id` int(11) NOT NULL auto_increment,
  `delatnost_id` int(11) NOT NULL,
  `naziv` varchar(32) NOT NULL,
  `opis` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `delatnost_id` (`delatnost_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `besplatni_oglas`
--

INSERT INTO `besplatni_oglas` (`id`, `delatnost_id`, `naziv`, `opis`) VALUES
(1, 1, 'Točak', 'Vulkanizerska radnja'),
(2, 1, 'Ipak se okreće', 'Vulkanizerska radnja Ipak se okreće se nalazi na autoputu Beograd-Niš kod naplatne rampe.'),
(3, 1, 'Ekser', 'Vulkanizerska radnja Ekser'),
(4, 1, 'Šraf u gumi', 'Vulkanizerska radnja - Šraf u gumi'),
(5, 2, 'Automehaničar Veca', 'Opis...'),
(6, 2, 'Bombina', 'Automehaničarska radnja'),
(7, 4, 'Milan', 'Autoperionica Milan'),
(8, 6, 'Delta Marketing', 'Naša firma se bavi ispitivanjem tržišta'),
(9, 10, 'Kozara', 'Bioskop Kozara'),
(10, 14, 'Casablanca', 'Noćni klub Casablanca'),
(11, 15, 'Restoran Torlak', 'Nalazi se pored stadiona FK Torlak u Kumodražu.'),
(12, 12, 'Muzej Nikola Tesla', 'Obavezno posetite muzej najvećeg srpskog naučnika i izumitelja.');

-- --------------------------------------------------------

--
-- Table structure for table `delatnost`
--

CREATE TABLE `delatnost` (
  `id` int(11) NOT NULL auto_increment,
  `grupa_id` int(11) NOT NULL,
  `naziv` varchar(255) NOT NULL,
  `seo` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `grupa_id` (`grupa_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `delatnost`
--

INSERT INTO `delatnost` (`id`, `grupa_id`, `naziv`, `seo`) VALUES
(1, 1, 'Vulkanizer', 'vulkanizer'),
(2, 1, 'Automehaničar', 'autmehanicar'),
(3, 1, 'Autoelektričar', 'autoelektricar'),
(4, 1, 'Auto perionice', 'auto-perionice'),
(5, 2, 'Ispitivanje tržišta i javnog mnjenja', 'ispitivanje-tržišta-javnog-mnjenja'),
(6, 2, 'Maketing agencije', 'marketing-agencije'),
(7, 3, 'Nameštaj - proizvodnja', 'nameštaj-proizvodnja'),
(8, 3, 'Stakleni i keramički materijali', 'stakleni-keramicki-materijali'),
(9, 3, 'Metali i metalni proizvodi', 'metali-metalni-proizvodi'),
(10, 4, 'Bioskopi', 'bioskopi'),
(11, 4, 'Pozorišta', 'pozorista'),
(12, 4, 'Muzeji', 'muzeji'),
(13, 4, 'Noćni klubovi', 'nocni-klubovi'),
(14, 4, 'Restorani', 'restorani'),
(15, 4, 'Biblioteke', 'biblioteke');

-- --------------------------------------------------------

--
-- Table structure for table `grupa`
--

CREATE TABLE `grupa` (
  `id` int(11) NOT NULL auto_increment,
  `naziv` varchar(255) NOT NULL,
  `seo` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `grupa`
--

INSERT INTO `grupa` (`id`, `naziv`, `seo`) VALUES
(1, 'Automobilizam', 'autmobilizam'),
(2, 'Marketing i promocija', 'marketing-promocija'),
(3, 'Proizvodnja i preprada materijala', 'proizvodnja-prerada-materijala'),
(4, 'Zabava i izlasci', 'zabava-izlasci');

-- --------------------------------------------------------

--
-- Table structure for table `placeni_oglas`
--

CREATE TABLE `placeni_oglas` (
  `id` int(11) NOT NULL auto_increment,
  `delatnost_id` int(11) NOT NULL,
  `seo` varchar(32) NOT NULL,
  `naziv` varchar(32) NOT NULL,
  `opis` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `seo` (`seo`),
  KEY `delatnost_id` (`delatnost_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `placeni_oglas`
--

INSERT INTO `placeni_oglas` (`id`, `delatnost_id`, `seo`, `naziv`, `opis`) VALUES
(1, 1, 'bracun', 'Bračun', 'Najbolji vulkanizer u Hrvatskoj...'),
(2, 1, 'mijic', 'Mijić', 'Mijić vulkanizer Vama na usluzi već 30 godina...'),
(3, 2, 'autoelektricar-voja', 'Autoelektričar Voja', 'Sa nama ste uvek sigurni...'),
(4, 9, 'metal-coop', 'Metal Coop', 'Izrada svih vrsta predmeta od metala'),
(5, 10, 'bioskop-vozdovac', 'Bioskop Voždovac', 'Jedan od najstarijih bioskopa u gradu. Nalazi se svega jedan kilometar od Autokomande'),
(6, 10, 'bioskop-balkan', 'Bioskop Balkan', 'Opis za bioskop Balkan'),
(7, 13, 'night-club-laguna', 'Night club Laguna', 'Uživo muzika svako veče'),
(8, 13, 'splav-lukas', 'Splav Lukas', 'Najpoznatiji beogradski splav!'),
(9, 11, 'stupica', 'Stupica', 'Pozorište Stupica opis...');

[DejanVesic]

Evo preko pogleda (sad probao na MySQL 5.0.45):

Prvo pogled:

CREATE VIEW Oglasi AS  
SELECT Del.grupa_id AS GrupaID, Del.id AS Delatnost_ID, Del.naziv AS DelatnostNaziv, Bo.id AS OglasID
FROM delatnost Del, besplatni_oglas Bo
WHERE Bo.id = Del.id
UNION ALL
SELECT Del.grupa_id AS GrupaID, Del.id AS Delatnost_ID, Del.naziv AS DelatnostNaziv, Po.id AS OglasID
FROM delatnost Del, placeni_oglas Po
WHERE Po.id = Del.id

A onda i upit:

SELECT `GrupaID`, `Delatnost_ID`, `DelatnostNaziv`, Count(*) 
FROM `Oglasi` 
Group By `GrupaID`, `Delatnost_ID`, `DelatnostNaziv`

Gornji upit možeš lepo filtrirati po grupi i ispisati ostala polja i to je to (barem se nadam )

[salebab]

Verovatno si mislio WHERE bo.delatnost_id = del.id pri kreiranju view-a...
Mana je sto ne prikazuje sve delatnosti, nego samo one u kojima ima oglasa. No, resicu to sa ovim upitom:

SELECT d.grupa_id, d.id, d.naziv, COUNT(o.oglas_id)
FROM delatnost as d
LEFT JOIN oglasi as o
  ON o.delatnost_id = d.id
WHERE d.grupa_id = 4
GROUP by d.grupa_id, d.id

A u view tabeli cu onda drzati samo kljuceve grupa_id, delatnost_id, oglas_id

Hvala na pomoci

[DejanVesic]

Originalno napisao salebab

Verovatno si mislio WHERE bo.delatnost_id = del.id pri kreiranju view-a...

Da, naravno, greška u brzini.

Mana je sto ne prikazuje sve delatnosti, nego samo one u kojima ima oglasa. No, resicu to sa ovim upitom:

Nisi tražio No, snašao si se

Hvala na pomoci

Nema na čemu - dosta dobro mi ide taj SQL

[salebab]

I na kraju, na serveru se nalazi mysql 4, a view tu ne radi

[DejanVesic]

Ništa, onda mora teška artiljerija:


- Kreiraš tabelu koja ima strukturu kao pogled:

`GrupaID`, `Delatnost_ID`, `BrojOglasaPlacenih`, `BrojOglasaNeplacenih`

- Kreiraš Trigger-e nad tabelama besplatni_oglas, placeni_oglas, delatnost koji će:

Placeni / Neplaceni (za delatnost_id):

1. Na insert da uvećaju odgovarajuće polje iz sloga gornje tabele
2. Na delete da smanje odgovarajuće polje iz sloga gornje tabele
3. Na update da smanje odgovarajuće polje iz sloga gornje tabele za staru vrednost sloga a da uvećaju odgovarajuće polje za novu vrednost sloga

Delatnost:

1. Na insert da dodaju slog gornjoj tabeli
2. Na delete da ga obrišu

Ovo ti je najčistije rešenje za agregatnu tabelu; jedino moraš da se pobrineš da kreneš od "prazne" baze, bez slogova u te tri tabele, da bi trigeri mogli sve to lepo da popune.