relacje na 3 różnych tabelach. Opcje

[acztery]

Witam,

mam taki małe zapytanie SQL. Możę powiem odrazu co ono robi

1.Listuję liste dokumentów
2.Kazdy ma pole UserId -> łączymy sie z tabelą user i wydobywamy wszystko dane z tej tabeli dla danego usera
3.Każdy dokument ma swoje Id i terach chce sie polaczyc z tabela documents_con i policzyc ile innych dokumentów jest przypisanych do danego i zapisac do do kolumny np countDocCon ( ... As countDocCon ).

ja mam takie coś ale wyswetla mi nie tak jak bym ( wszedzie 0 nie liczy )

[SQL] pobierz, plaintext 
SELECT * , count( ifnull( documents_con.DocId, 0 ) ) -1 AS value2
FROM `documents` LEFT JOIN `documents_con` ON `documents_con`.`DocId` = `documents`.`Id` 
RIGHT JOIN `user` ON `user`.`user_id` = `documents`.`UserId` 
WHERE (
documents.Cat = 'HydePark_'
) AND (
Act = 'Y'
) AND (
`documents`.`Lang` = 'Polski'
)
GROUP BY documents.Id
LIMIT 0, 30
[SQL] pobierz, plaintext 

struktura

3 tabel nizej

Kod

CREATE TABLE `documents` (
  `Id` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Lang` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Title` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Desc` text collate utf8_polish_ci NOT NULL,
  `CreateDate` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Password` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Cat` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Time` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Act` enum('Y','N') collate utf8_polish_ci NOT NULL default 'Y',
  `UserId` varchar(255) collate utf8_polish_ci NOT NULL,
  KEY `Id` (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

CREATE TABLE `documents_con` (
  `Id` varchar(225) collate utf8_polish_ci NOT NULL default '',
  `DocId` varchar(225) collate utf8_polish_ci NOT NULL default '',
  `DocIdCon` varchar(225) collate utf8_polish_ci NOT NULL default '',
  `Time` varchar(225) collate utf8_polish_ci NOT NULL default '',
  `Lang` varchar(200) collate utf8_polish_ci NOT NULL default '',
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;


CREATE TABLE `user` (
  `user_id` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `login` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `password` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Email` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `UserType` enum('Private','Company') collate utf8_polish_ci NOT NULL default 'Private',
  `CompanyName` varchar(255) collate utf8_polish_ci default NULL,
  `NIP` varchar(255) collate utf8_polish_ci default NULL,
  `ContactPerson` varchar(255) collate utf8_polish_ci default NULL,
  `Fname` varchar(255) collate utf8_polish_ci default NULL,
  `Mname` varchar(255) collate utf8_polish_ci default NULL,
  `Lname` varchar(255) collate utf8_polish_ci default NULL,
  `BirthYear` varchar(255) collate utf8_polish_ci default NULL,
  `Street` varchar(255) collate utf8_polish_ci default NULL,
  `PostCode` varchar(255) collate utf8_polish_ci default NULL,
  `City` varchar(255) collate utf8_polish_ci default NULL,
  `Country` varchar(255) collate utf8_polish_ci default NULL,
  `PhoneNumber` varchar(255) collate utf8_polish_ci default NULL,
  `Lang` varchar(255) collate utf8_polish_ci NOT NULL default 'pl',
  `Active` enum('Y','N') collate utf8_polish_ci NOT NULL default 'N',
  `CreateDate` varchar(255) collate utf8_polish_ci NOT NULL default '0000-00-00 00:00:00',
  `Blocked` enum('N','Y') collate utf8_polish_ci NOT NULL default 'N',
  `Group` varchar(255) collate utf8_polish_ci NOT NULL default '',
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

cos takiego działa tyko ze teraz nie pobiera Id dokumentu

[SQL] pobierz, plaintext 
SELECT *,  ifnull(count(documents_con.Id),0) AS CountDocCon  
 FROM `documents` LEFT JOIN `documents_con`  ON `documents_con`.`DocIdCon`=`documents`.`Id`  
 LEFT JOIN `user`  ON `user`.`user_id`=`documents`.`UserId`   
 WHERE ( documents.Cat = 'HydePark_' ) AND ( documents.Lang = 'Polski' ) 
 GROUP BY Title
[SQL] pobierz, plaintext 

moze ktos wie co i jak?

---
EDIT rozwiązane.

dla zainteresowanych

[PHP] pobierz, plaintext 
<?php
public function lstDocuments($_activ='',$_limit='')
	{
		$db = parent::Connect();
		$pis = DB_DataObject::factory('user');
		$pis2 = DB_DataObject::factory('documents_con');
 
		$db->query("set names 'utf8'");
		if ($this->_where <> '') {
			$db->whereAdd("documents.Cat = '$this->_where'");
		}
		if ($this->_lang <> '') {
			$db->whereAdd("documents.Lang = '$this->_lang'");
		}else{
			$db->Lang = Lang::getDisLang();
		}
 
		if ($this->_f != '' or $this->_t !='')
		{
			$db->limit($this->_f,$this->_t);
		}
 
		if ($this->_order <> '') {
			$db->Orderby("documents.".$this->_order);
		}
		if ($_activ <> '') {
			$db->whereAdd("documents.Act = 'Y'");
		}
		$db->selectAdd('documents.Id as Id');
		$db->joinAdd($pis2,"LEFT");
		$db->joinAdd($pis,"LEFT");
		$db->selectAdd('ifnull(count(documents_con.Id),0) as CountDocCon');
		$db->groupBy(Title);
		$db->find();
		while($db->fetch()) {
			$result['data'][] = $db->toArray();
		}
		return $result = isset($result) ? $result : null;
	}
?>
[PHP] pobierz, plaintext 

Ten post edytował acztery 7.03.2007, 15:13:46