ON JOIN

1. Návštěvník 48DF1D76 29. 10. 2007 03:36

   Ahoj mam problemy se slozitejsimy SQL dotazy napr. mam tri tabulky forums, projects, messages na forums je napojene messages, forums.forum_id = messages.message_id vypisu si forums a project a pritom si vypisu pocet message, ve foru Select f.forum_id, p.project_id count(m.message_id) FROM forums as f, projects as p left join messages as m on m.message_forum=f.forum_id where f.forum_id = p.project_id group by f.forum_id coz nahlasi #1054 - Unknown column 'f.forum_id' in 'on clause' pritom modifikace Select f.forum_id, p.project_id count(m.message_id) FROM forums as f left join messages as m on m.message_forum=f.forum_id group by f.forum_id funguje a funguje i modifikace Select f.forum_id, p.project_id count(m.message_id) FROM forums as f, projects as p left join messages as m on m.message_forum=1 where f.forum_id = p.project_id group by f.forum_id ale samorejmne ne z vysledkem ktery bych chtel trochu slozitejsi dotazy sem tu mel pred par lety a ted koukam ze nejedou.

2. Návštěvník 2F348341 30. 10. 2007 06:16

   Tento problem znam, na nejake verzi MySQL to projde a na nektere ne. Ja Ti reknu, v cem je rozdil - v prvnim priklade mas FROM dva tabulky a v dalsich dvou jenom jednu. Kdyz mas tri tabulky, pouzijes 3x LEFT JOIN pres indexy, pak by to melo projit ;) Zkratka kolik mas tabulek, tolik musis mit JOINu. Viz muj priklad: SELECT COUNT(".$this->table_gb.".Id) AS comments,".$this->table_users.".Id AS userid,".$this->table_news.".Id,".$this->table_news.".Type,".$this->table_news.".Date,Nick,Header FROM ".$this->table_news." LEFT JOIN ".$this->table_users." ON (".$this->table_news.".Id_user=".$this->table_users.".Id) LEFT JOIN ".$this->table_gb." ON (".$this->table_gb.".Id_parent=".$this->table_news.".Id) AND (".$this->table_gb.".Type=0) WHERE (".$this->table_news.".Type=$typ) GROUP BY ".$this->table_news.".Id ORDER BY ".$this->table_news.".Date DESC

← Zpět na všechna témata