|
|
Posted by Erland Sommarskog on 11/23/05 00:43
Marco van de Voort (marcov@stack.nl) writes:
> I'm a relative db newbie, and total SQL server newbie and I garbled up
> the following query ( :xxx are parameters that are filled in by the
> app)
>
>
> SELECT PERSON.*,
> CASE ISNULL(cap.captiveid, 0) WHEN 0 THEN cast(0 AS bit)
> ELSE cast(1 AS bit) END AS ItemPresent
> FROM PERSON p
> INNER JOIN ADRES a ON (a.ADRESID = p.ADRES_ID)
> LEFT JOIN CAPTIVE cap ON (a.ORGANISATIONID = CAP.BUYER_ID)
> INNER JOIN DEFAUCTION auc
> ON (auc.LESSOR = cap.ORGANISATION_ID)
> WHERE
> (ITEMPRESENT=0 AND :MYCAPTIVE=0) OR
> (auc.DEFID=:DEFID AND
> ((:MYCAPTIVE = 0 AND CAP.CAPTRELATIONTYPE = 1) OR
> (:MYCAPTIVE= 1))
> )
>
> It seems sqlserver doesn't allow a calculated field as itempresent in the
> where clause. Does sb see a workaround or alternate way ?
Does not allow? Do you get an error message, please post it.
> The basic problem is that there are persons that are not "attached" via
> the joins to the defauction row where defauction.defid=<parameter> that
> should be included if :MYCAPTIVE=0, but not if
>:MYCAPTIVE=1 (hence the left join)
Since you did not give a clear description of what is going wrong, this
will have to be guessworks.
But there is one thing that is possibly wrong with the outer join, so
permit me to discuss that.
If you say:
SELECT ...
FROM a
LEFT JOIN b ON a.col = b.col
WHERE b.othercol = 1
You have in fact changed the outer join into an inner join. This is
because first you take all rows in a, and hang on all columns in b.
Where there is a macthing row in b, there will be values in those
columns, else there will be NULLs. Thats FROM a LEFT JOIN b.
Once the from clause (which includes all JOIN operators) has resulted
into a table, this table is filtered by WHERE. Since WHERE here says
b.othercol = 1, then all rows with NULL in b.othercol are dropped from
the result set.
If you instead do:
SELECT ...
FROM a
LEFT JOIN b ON a.col = b.col
AND b.othercol = 1
It's a different story. Now the JOIN results in a table where the columns
from b are NULL when the key from a did not match - or othercol had any
other value than 1.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|