Can any one explain this query behavior

[pai]

db_TBO db_TBT
-----------------------------
--------------------------------------------------------
TBOID | Date TBTID | TBOID | Date
---------------------------- ---------------------------------------------------------
rp01 | 01/08/2006 ap01 | rp01 | 02/08/2006
many rows ap02 | rp01 | 05/08/2006
ap03 | rp03 | 04/08/2006

I want to find TBTTD field of table db_TBT
who have of db_TBO date db_TBT table date
and TBOID should be 'rp01' of both tables

when I give a query as it works as I needed

Select TBT.Date
from db_TBO TBO , db_TBT TBT
where TBT.TBOID = 'rp01'
and TBO.TBOID ='rp01'
and TBO.Date TBT.Date

My doubt is when I run the following query
" Select TBT.Date from db_TBO TBO , db_TBT TBT where TBT.TBOID = 'rp01'
"
it gives me more than 7 records

when I run the query using some change
" Select TBT.Date , TBT.TBID from db_TBO TBO , db_TBT TBT where
TBT.TBOID = 'rp01' "
it gives more rows than previous row

Can u give me any explations.

Thanks
Pai

[Hugo Kornelis]

On 23 Aug 2006 22:54:21 -0700, pai wrote:

> db_TBO db_TBT
-----------------------------
--------------------------------------------------------
TBOID | Date TBTID | TBOID | Date
---------------------------- ---------------------------------------------------------
rp01 | 01/08/2006 ap01 | rp01 | 02/08/2006
many rows ap02 | rp01 | 05/08/2006
ap03 | rp03 | 04/08/2006

I want to find TBTTD field of table db_TBT
who have of db_TBO date db_TBT table date
and TBOID should be 'rp01' of both tables

(snip)

>
My doubt is when I run the following query
" Select TBT.Date from db_TBO TBO , db_TBT TBT where TBT.TBOID = 'rp01'
"
it gives me more than 7 records

Hi Pai,

Of course it does. The FROM clause lists two tables, comma-seperated.
That means that you'll get a cross join, also known as cartesian
product, between the two tables: each row from the first table will be
paired with each row from the second table. After that (*), the WHERE
clause removes rows based on TBT.TBOID. The result set willl include
only TBT-rows with TBOID equal to 'rp01' - but each of those rows will
still be paired against each of the rows in db_TBO.

>when I run the query using some change
" Select TBT.Date , TBT.TBID from db_TBO TBO , db_TBT TBT where
TBT.TBOID = 'rp01' "
it gives more rows than previous row

I don't understand this. The only difference between this query and the
previous query is the addition of one more column in the WHERE clause.
That shoould never affect the number of rows returned. Are you sure that
you didn't make a mistake when you copied the SQL into your message?

(snipped from above:)

>when I give a query as it works as I needed

Select TBT.Date
from db_TBO TBO , db_TBT TBT
where TBT.TBOID = 'rp01'
and TBO.TBOID ='rp01'
and TBO.Date TBT.Date

Indeed. This query also starts (*) with the cross join, but then retains
only rows with both TBT.TBOID and TBO.TBOOID equal to 'rp01'. That means
that you're left with each TBT-row for 'rp01' paired to each TBO-row for
'rp01'. These results are then further filtered by the date comparison.

I think your problems arise out of the use of the "old-style" FROM
clause. The newer style, with explicit joins, makes it much harder to
make this kind of mistakes since it forces you to explicitly write down
the join criteria:

SELECT some columns
FROM db_TBO AS TBO
INNER JOIN db_TBT AS TBT
ON TBO.TBOID = TBT.TBOID
WHERE TBO.Date TBT.Date;

(*) The order of evaluation described here is only a logical explanation
of the process. The query optimizer is free to (and definitely will, in
this case) change the order of evaluation, as long as the results remain
the same.

--
Hugo Kornelis, SQL Server MVP