Any other choice than left outer join?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Any other choice than left outer join??

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2008-04-13 : 08:27:54

i have three tables company as co, Procurement as po,contracts as cr
Co_id as primary for co
co_id forign for po
co_id forign for cr

i want the query to get me only one record for every co_id if it has a company in certain PO or CR ( i used max fnctn to get me only one record)

result:

CO_ID,PODOCNO,CRDOCNO
----- ------- -------
1 350 400
2 355 1064
3 NULL 500
4 600 NULL

I used the left outer join in this manner

SELECT CO.CO_ID, MAX(PR.DOCNO) AS pO_DOCNO ,MAX(CR.DOCNO) AS CR_DOCNO
FROM COMPANY CO lEFT OUTER JOIN PROCUREMENT PR ON OG.CO_ID=PR.CO_ID lEFT OUTER JOIN CONTRACT CR ON CO.CO_ID=CR.CO_ID
GROUP BY OG.CO_ID

the result is ok but the problem its taking infinte time if i add more tables to the outer join, i have more tables and each with huge number of records

any better way to do this ?? its true performance is a big deaaaal

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-13 : 09:37:25

What indexes do you have and how big are the tables.
Have you looked at the query plan?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-13 : 11:33:50

You could probably look to see if you can spot any table scans in your query execution plan.

ann06
Posting Yak Master

171 Posts

Posted - 2008-04-14 : 05:43:41

no the problem is for every record in company there is like 100 records in po and 100 records in contracts so when doing outer join its returning 100 x 100 record and if there is another table it will multiply also with the records of the third tabled
ex:
co_id po_id co_id CR_id
1 10 1 20
1 11 1 23
1 15 1 25
1 17

when we do the outer join result will be 4 x 3 = 12 records

co_id po_id cr_id
1 10 20
1 11 20
1 15 20
1 17 20
1 10 23
1 11 23
1 15 23
1 17 23
1 10 25
1 11 25
1 15 25
1 17 25

any better way to do this join ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 05:47:50

quote:
Originally posted by ann06

SELECT CO.CO_ID, MAX(PR.DOCNO) AS pO_DOCNO ,MAX(CR.DOCNO) AS CR_DOCNO
FROM COMPANY CO lEFT OUTER JOIN PROCUREMENT PR ON OG.CO_ID=PR.CO_ID lEFT OUTER JOIN CONTRACT CR ON CO.CO_ID=CR.CO_ID
GROUP BY OG.CO_ID

That this code, there is absolutely no guarantee that the two values derives from same record...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 05:50:47

If above is of no matter, try this

SELECT		CO.CO_ID,
		PR.DOCNO,
		CR.DOCNO
FROM		COMPANY AS CO
LEFT JOIN	(
			SELECT		CO_ID,
					MAX(DOCNO) AS DOCNO
			FROM		PROCUREMENT
			GROUP BY	CO_ID
		) AS PR ON PR.CO_ID = CO.CO_ID
LEFT JOIN	(
			SELECT		CO_ID,
					MAX(DOCNO) AS DOCNO
			FROM		CONTRACT
			GROUP BY	CO_ID
		) AS CR ON CR.CO_ID = CO.CO_ID

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 06:28:53

>> no the problem is for every record in company there is like 100 records in po and 100 records in contracts so when doing outer join its returning 100 x 100 record and if there is another table it will multiply also with the records of the third tabled

That's a problem with your code not with the left outer join.
What do you want to return?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ann06
Posting Yak Master

171 Posts

Posted - 2008-04-14 : 07:20:52

Thanks Very Much so the problem was from the select i should minimize the select when using outer join?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 07:28:58

If you refer to the post made 04/14/2008 : 05:50:47, the answer is yes.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources