|
|
Posted by Hugo Kornelis on 10/02/84 11:56
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
Navigation:
[Reply to this message]
|