--- In firebird-support@yahoogroups.com, <rambabu.piridi@...> wrote:

>
> I have two tables. User and User Properties.
> I am doing table "User" left outer join Table "UserProperties".
>
> but i want to do the join some thing like this:
> Select rows from 20 to 40 from table User and then doing the join with
> these rows with the table UserProperties.
>
> Can you please suggest the query for this.
>

Please don't use 'super extremely really totally more urgent than
anyone elses problems because I am more important than them' type
subject lines. You will end up in the spam filter otherwise, and it is
obvious from the context the true urgency.

According to the SQL standard, Queries return records in an arbitrary
order unless you explicitly include an 'Order By' clause. This is a
good thing, if you just want to return particular rows and do not care
about the order, you don't need to wait for the database engine to
sort them all. When inserting a new record, you don't need to shuffle
the records around everywhere to put the value in the correct
location. When deleting a record, you don't need to move all
subsequent records or waste space.

So it does not make sense to ask for rows 20 to 40, you are asking the
engine to return to you 20 arbitrary records, but not the first 20
arbitrary records it encounters. If you are truly interested in 20
arbitrary records, then what is wrong with the first 20?

You are doing a positional query (obviously because you are using
skip), and for it to return a predictable bunch of records, you must
include ORDER BY.

Secondly, it does not make sense to me to return these values in the
same query, because you presumably have a 1:many relationship between
users and usersproperties.

Why would you want a resultset that looks like:

UserID UserName Password Property PropertyValue
------ -------- -------- -------- -------------
1 Adam 123 1 1
1 Adam 123 2 1
1 Adam 123 3 1
2 Fred 456 1 2
2 Fred 456 2 4

It makes little sense. I can see the use of returning:

UserID UserName Password
------ -------- --------
1 Adam 123
2 Fred 456

and another query returning:

UserID Property PropertyValue
------ -------- -------------
1 1 1
1 2 1
1 3 1
2 1 2
2 2 4

The reason you can't use the first skip in a subselect is because of a
bug, the subselect is executed once per record of the main select, so
it probably wouldn't be very efficient anyway. You could do what you
are after within a stored procedure, but I agree with Ann that it
appears you are attacking this problem using the concept of positional
updates, which is going to be a slow design at best.

Adam