Participant

Answered

left outer join and maxrows problem

Forum|Forum|18 years ago
September 19, 2007
11 replies
1453 views

I'm having a problem with the maxrows and a sql join. My left table has some records that I want listed, 25 max per page. These records have some associated notes in another table thats outer joined. When I specify maxrows in my cfoutput tag it includes the rows from my outer joined table. So I might only get 5 records from my left table and 20 from my joined table. What I want is 25 records from my left table and however many records from the other table that might be associated with those 25 records. Is this possible?

This topic has been closed for replies.

Correct answer Steve Sommers

I would do two queries and avoid the outer join in this case:

C

cmitchellAuthor

Participant

I have MSSQL 2k5 for those that asked.

I went ahead and did Steves original idea and its working great. Having another query in my output and hitting the DB another 25 times isn't ideal but it works for now. I really like all the ideas comming from this thread, I will be testing them out and most likely make a change based on my specific situation. Thanks a bunch everyone.

C

cf_dev2

Inspiring

Stressed_Simon,

What is so complicated about using ROW_NUMBER() ? IMO its simpler than using a table variable. Though its a valid approach as well. Of course you need to be running sql server 2005 to use row_number().

Btw, table variables work in sql 2000 (not just 2005).

S

Stressed_Simon

Inspiring

cf_dev2 on large datasets Row_Number() can slow things down, but I do agree with Steve Sommers that it is the easiest way to achieve pagination. I would still suggest adding the initial data to a temporary table before doing the left join, we take this approach at our place on the advice of our DBA and it is way faster and reduces the amount of locking on your tables.

C

cf_dev2

Inspiring

quote:

Originally posted by: Stressed_Simon
cf_dev2 on large datasets Row_Number() can slow things down, but I do agree with Steve Sommers that it is the easiest way to achieve pagination. I would still suggest adding the initial data to a temporary table before doing the left join, we take this approach at our place on the advice of our DBA and it is way faster and reduces the amount of locking on your tables.

We just installed 2005 on one server, and I haven't noticed any big performance issues yet. But then again I haven't used it on any huge results or without TOP.

Using a table variable (or a #temp table) is a viable option, though you would need to add pagination (set of X) logic. Another option is to use the old sql2K dual TOP method either as derived table or used in combination with the @table variable technique.

S

Stressed_Simon

Inspiring

Oh my God you guys are over complicating things, this is a prime candidate for a temporary table, here is the SQL Server 2005 syntax, I do not know what fields are in your tables so I am just guessing here but here goes:-

DECLARE @tSupport (
keyfield int primary key,
supportName varchar(200)
)

-- populate table with the 25 we need
INSERT INTO tSupport
SELECT TOP 25 keyfield, supportName
FROM itsupport

-- now get results joined to it support notes table
select *
from @tSupport A
left outer join itsupportnotes B
on A.keyfield = B.keyfield2

This is the way to do it and will outperform all the other ways. If you are not on SQL Server 2005 you will need to check you db documentation.

S

Steve Sommers

Legend

quote:

Originally posted by: Stressed_Simon
Oh my God you guys are over complicating things,...temp table...

This works fine for the top 25 but I don't see how this approach will work with page 2, 3, 4 or whatever where you need a middle 25.

C

cf_dev2

Inspiring

cmitchell,

I see a newsgroup posting just popped up with this suggestion. But if you're using sql server 2005 you can use Row_Number() like this. Though obviously you should use cfqueryparam instead of using url variables directly.

SELECT A.Row, A.KeyField, A.SomeField, B.Notes
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY SomeField ASC) AS Row,
KeyField, SomeField
FROM itsupport
)
AS A LEFT JOIN itsupportnotes B ON A.KeyField = B.KeyField2
WHERE A.Row >= #url.startRow# AND A.Row <= #url.endRow#

Edit - you can also combine it with TOP X

G

Grizzly9279

Participating Frequently

RE: cmitchell

You said:
"I had thought of this but I would have a hard time figuring out what the value of the startrow attribute would be for displaying page 2 of the results."

Couldn't you just say something like: <a href="{SELF}?start=#(url.start+25)#">next page</a>
??

S

Steve Sommers

Legend

With a little added brain grease, you can reduce the queries down to2 from 26 by using the first result set; something like this:

<cfquery name="all">
...
</cfquery>
<cfset keylist="">
<cfloop query="all"><cfset keylist=ListAppend(keylist,key)></cfloop>

<cfquery name="notes">
...
where key2 in ( #keylist# )
</cfquery>

Obviously the syntax is not 100%.

In thinking about this more, you could do your original query with the join here. Do the prequery to get your 25 items without any joins. Do the second query based on the keys returned in the first and this second query does your joins.

N

Newsgroup_User

Inspiring

You could probably do this with more advanced SQL using a sub-select of
some sort. The exact syntax may depend on which DBMS you are using but
here is a first idea.

SELECT aField, bField, cField
FROM aTable a left outer join (Select aField, cField FROM bTable WHERE
ROWNUM <= 25) b ON a.aField = b.aField

I have not done joins on a sub-select like this, but I think it is
possible. Also, as mentioned, the syntax to restrict the number of rows
returned in a query is very Database Management Specific. My example is
the Oracle version.

C

cf_dev2

Inspiring

Yes, two queries would work. Though it does hit the db 25 times per page.

S

Steve Sommers

Legend

quote:

Originally posted by: cf_dev2
Though it does hit the db 25 times per page.

Yes, but I've found that while it may not seem efficient, it is easier to maintain and support and depending on the data, it may be more efficient than a huge joined query where you're only using a subset of the results.

C

cf_dev2

Inspiring

quote:

Originally posted by: Steve Sommers
Yes, but I've found that while it may not seem efficient, it is easier to maintain and support and depending on the data, it may be more efficient than a huge joined query where you're only using a subset of the results.

True, there are cases where separate queries is a better option.

quote:

Originally posted by: cmitchell
I was hoping to get this into a single query but your suggestion looks like my best alternative.

What version of sql server are you using? 2000 or 2005

C

cf_dev2

Inspiring

Grizzly9279 that's what I was thinking, but I wasn't sure you could use a cfbreak to exit a cfoutput loop.

G

Grizzly9279

Participating Frequently

RE: cf_dev2

You're absolutely right, <cfbreak> doesn't work within a <cfoutput> query loop, DOH!

Here is a "hacky" alternative that could work instead.

C

cmitchellAuthor

Participant

Thanks for the reply Scott. Any Ideas on how I might accomplish my goal? I was kind of hoping the solution was simple and I was just overlooking the obvious.

G

Grizzly9279

Participating Frequently

You could just manage the start/end rows manually using a counter variable.

Example:

S

Steve SommersCorrect answer

Legend

I would do two queries and avoid the outer join in this case:

Show more replies

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded