Podzapytanie/join zliczające rekordy z jednej tabeli do innej

pytanie zadane 15 września 2015 w SQL, bazy danych przez Janusz92 Bywalec (2,150 p.)

Witam.

Chcę stworzyć zapytanie MySQL, które wybierze z tabeli "images" wszystkie rekordy i dodatkowo przy każdym z rekordów będzie dodatkowa kolumna, w której zliczy się ilość wszystkich komentarzy (z tabeli "comments") dla danego rekordu z "images".

Czyli wynik ma wyglądać mniej więcej tak:

id	title	ile_komentarzy
1	obrazek 1	13
2	obrazek 2	24

Tabela images:

CREATE TABLE IF NOT EXISTS `images` (
  `img_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `title` varchar(32) NOT NULL,
  `descr` varchar(2048) NOT NULL,
  `datetime` datetime NOT NULL,
  `onmain` datetime NOT NULL,
  `author` int(11) NOT NULL,
  `main` tinyint(1) NOT NULL,
  PRIMARY KEY (`img_id`),
  KEY `author` (`author`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=204 ;

Tabela comments:

CREATE TABLE IF NOT EXISTS `comments` (
  `com_id` int(11) NOT NULL AUTO_INCREMENT,
  `author` int(11) NOT NULL,
  `post` int(11) NOT NULL,
  `com` varchar(2048) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`com_id`),
  KEY `author` (`author`),
  KEY `post` (`post`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ;

2 odpowiedzi

odpowiedź 15 września 2015 przez Boshi VIP (100,240 p.)
edycja 15 września 2015 przez Boshi

Mam dziwne wrażęnie, że ta baza jest źle zaprojektowana? po co tutaj jest autor w obu tabelach? to jakaś osobna tabela czy jak ?

komentarz 15 września 2015 przez Janusz92 Bywalec (2,150 p.)

Istnieje trzecia tabela z użytkownikami.

Zapytanie, które zwraca mi coś sensownego to:

SELECT img_id, title, COUNT(comments.com) AS ile_kom FROM images INNER JOIN comments ON images.img_id=comments.post GROUP BY img_id

Ale w takiej postaci zwraca mi tylko rekordy, w których jest co najmniej 1 komentarz.

Czy jest możliwość, aby wyświetlone zostały również rekordy "z zerami"?

komentarz 15 września 2015 przez Tomasz90 Nałogowiec (25,140 p.)

left joina musisz użyć zamiast inner

komentarz 15 września 2015 przez Janusz92 Bywalec (2,150 p.)

Działa świetnie, dzięki. Właśnie byłem w trakcie studiowania innych typów JOIN'ów :)

Ostatecznie zapytanie wygląda w ten sposób:

SELECT img_id, title, COUNT(comments.com) AS ile_kom FROM images LEFT JOIN comments ON images.img_id=comments.post GROUP BY img_id

komentarz 15 września 2015 przez Boshi VIP (100,240 p.)

To dobrze, ale zastanawiam mnie dalej po co ci autor w tabeli images skoro masz go w tabeli comments? Przecież autor musi mieć jakiś komentarz?

komentarz 15 września 2015 przez Janusz92 Bywalec (2,150 p.)

W tabeli images author wskazuje na autora postu, natomiast w tabeli comments author jest autorem danego komentarza. (Nie upieram się przy tym, że tak jest dobrze, ale chyba tylko w taki sposób może to funkcjonować?)