SQL Server Performance Forum – Threads Archive

Join Query…

I need Company Information but the "T_Company" table have depends on the another 6 tables. The Company table have "1783" rows.I had excute the following query i have get more than 10418 rows. Query
—–
TRUNCATE TABLET_Company
INSERT INTO T_Company(nvcAccNo,nvcName,nvcSortName,nvcResultXML,nvcDetailXML,dtLastPopDate)
SELECTdistinct(C.AccountNumber),
C.Name,
CFCHI.Sortname,
‘<Company ID="’ + C.AccountNumber +
‘" Name = "’ + C.Name +
‘" City ="’ + C.City +
‘" State ="’ + C.State +
‘" DisplayLevel = "’ + ” +
‘" Country ="’ + C.Country +
‘" URL ="’ + ” +
‘" Logo ="’ + ” +
‘"/>’,
‘<Company ID="’ + C.AccountNumber +
‘" Name = "’ + C.Name +
‘" AddressLine1 ="’ + C.Address1 +
‘" AddressLine2 ="’ + C.Address2 +
‘" City ="’ + C.City +
‘" Country ="’ + C.Country +
‘" Telephone ="’ + C.CompanyTelephone1 +
‘" AltTelephone ="’ + C.CompanyTelephone2 +
‘" Fax ="’ + C.CompanyFax +
‘" URL ="’ + C.URL +
‘" FirstName ="’ + CFPI.FirstName +
‘" LastName ="’ + CFPI.LastName +
‘" Division ="’ + CFDCM.Division +
‘" EmailAddress ="’ + CFE.Emailaddress +
‘" Logo ="’+ ” +
‘" Description ="’ + C.Description +
‘" Category ="’ + SC.Name +
‘" LinkURL ="’ + ” +
‘" AttachmentFile ="’ + ”+
‘"/>’,
”
FROMSosland..T_CompanyCategory CC
JOINSosland..T_Company CONCC.AccountNumber = C.AccountNumber
JOINSosland..T_SolisCategory SCONCC.SolisCategoryID = SC.SolisCategoryID
JOINSosland..T_CF_CompanyHiddenInformationforofficeuseonlyCFCHIONCFCHI.AccountNumber = C.AccountNumber
JOINSosland..T_CF_PersonnelInformationonlyonepersonpublished CFPIONCFPI.AccountNumber = C.AccountNumber
JOINSosland..T_CF_DivisionCompanyMailAddressifzipdiffersfrompreviousaddress CFDCMONCFDCM.AccountNumber = C.AccountNumber
JOINSosland..T_CF_Emailaddress CFEONCFE.AccountNumber = C.AccountNumber Vadivel R
Software Engineer
Voice : 9894435837

---

1 Are you using SQL Server?
2 The query seems that you are using some where in Reports
3 Post table structures, with some sample and expected data Madhivanan Failing to plan is Planning to fail

---

– keep in mind distinct works at "the whole of the row"-level, except when grouping is used. – IMO you may be better off splitting the query in at least two parts :
one for the "SELECT distinct C.AccountNumber ,C.Name,CFCHI.Sortname"-part and the second to accomplish the text-concatenation-part
(because you may not want to include that into your distinct-clause)

---

finaly 100 posts [<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />][^][<img src=’/community/emoticons/emotion-3.gif’ alt=’‘ />)]

---

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by alzdba</i><br /><br />finaly 100 posts [<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />][^][<img src=’/community/emoticons/emotion-3.gif’ alt=’‘ />)]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">You will be congratulated when you hit 1000<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

---

1. What is cardinality of cc and sc relationship: 1-n, 1-1 or n-1?
2. What is cardinality of c and cfchi relationship: 1-n, 1-1 or n-1?
3. What is cardinality of c and cfpi relationship: 1-n, 1-1 or n-1?
4. What is cardinality of c and cfdcm relationship: 1-n, 1-1 or n-1?
5. What is cardinality of c and cfe relationship: 1-n, 1-1 or n-1? At least one of relationships mentioned has cardinality 1 to N. You have to use correlated query or group by to avoid multiple rows per c.AccountNumber.

---

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by alzdba</i><br /><br />finaly 100 posts [<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />][^][<img src=’/community/emoticons/emotion-3.gif’ alt=’‘ />)]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">You will be congratulated when you hit 1000<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Let’s extrapolize : 2002 – 2006 = 100 post <br />…. 40 years to go [?][?] [<img src=’/community/emoticons/emotion-3.gif’ alt=’‘ />)]<br /><br />I hope it’ll be sooner than that [^]

---

Some of it looks like a clasroom based question!
Anyway wait until the orginator comes back with the required information.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

]]>