DaniWeb IT Discussion Community - View Single Post - Problem using JOIN with six tables

Re: Problem using JOIN with six tables

May 12th, 2008

Try this code

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT	c.name, isNull(doc_A.Doc_A, 0) AS Doc_A, isNull(doc_B.Doc_B, 0) AS Doc_B, isNull(doc_C.Doc_C, 0) AS Doc_C, isNull(doc_D.Doc_D, 0) AS Doc_D
  FROM	tUsers u INNER JOIN tContacts c ON
	u.contact_id = c.id LEFT JOIN (SELECT user_id, count(*) AS Doc_A FROM tDocumentsA GROUP BY user_id) Doc_A ON
	c.id = Doc_A.user_id LEFT JOIN (SELECT user_id, count(*) AS Doc_B FROM tDocumentsB GROUP BY user_id) Doc_B ON
	c.id = Doc_B.user_id LEFT JOIN (SELECT user_id, count(*) AS Doc_C FROM tDocumentsC GROUP BY user_id) Doc_C ON
	c.id = Doc_C.user_id LEFT JOIN (SELECT user_id, count(*) AS Doc_D FROM tDocumentsD GROUP BY user_id) Doc_D ON
	c.id = Doc_D.user_id
  WHERE u.location = 1
select	c.name, isNull(doc_A.Doc_A, 0) as Doc_A, isNull(doc_B.Doc_B, 0) as Doc_B, isNull(doc_C.Doc_C, 0) as Doc_C, isNull(doc_D.Doc_D, 0) as Doc_D
  from	tUsers u inner join tContacts c on
	u.contact_id = c.id left join (select user_id, count(*) as Doc_A from tDocumentsA group by user_id) Doc_A on
	c.id = Doc_A.user_id left join (select user_id, count(*) as Doc_B from tDocumentsB group by user_id) Doc_B on
	c.id = Doc_B.user_id left join (select user_id, count(*) as Doc_C from tDocumentsC group by user_id) Doc_C on
	c.id = Doc_C.user_id left join (select user_id, count(*) as Doc_D from tDocumentsD group by user_id) Doc_D on
	c.id = Doc_D.user_id
  where u.location = 1

Hence Wijaya
www.ex-Soft.tk