Re: SQL join looks odd, and does not appear to function correctly.

John,

Not sure why you think this is "how Microsoft SQL" works when you say
that you used Crystal Report writer as a starting point. Thus the
statement is a Crystal statement and really has nothing to do with MS
SQL Server.

In any case, you are correct in that this will not give you what you want.

Basically, if you're looking for unmatched records and inner join is
most certainly the wrong join. You want to use a OUTER JOIN or an
EXCEPTION JOIN. Another possibility is a WHERE NOT EXISTS clause.

select count(*)
from INVENTORY_HEADER left exception join PREFERENCE_DETAIL
on INVENTORY_HEADER."SYSTEM_ID" = PREFERENCE_DETAIL."RESOURCE_ID"

Charles



On Mon, Feb 1, 2010 at 12:14 PM, jmmckee <jmmckee@xxxxxxxxxxxxxx> wrote:

I was trying to create a count of items in INVENTORY_HEADER not referenced by PREFERENCE_DETAIL.  I used Crystal Report Writer to generate a starting point.

But, it looks odd.  I don't understand the oj in the FROM clause.  Or, for that matter, why INNER JOINN was used to connect INVENTORY_HEADER and PREFERENCE DETAIL.  As presented below, I do not see items that are unreferenced.  If I change INNER JOIN to OUTER JOIN, I definitely get the wrong results.

Is this just how Microsoft SQL works, or is there some way to fix this?

John McKee


SELECT
   MAX(INVENTORY_HEADER."SYSTEM_ID"),
   MAX(INVENTORY_HEADER."INVENTORY_ID"),
   MAX(PREFERENCE_DETAIL."RESOURCE_ID"),
   count(preference_detail."RESOURCE_ID")
FROM
   { oj "ORSOSTEST"."ORSOSTEST"."INVENTORY_HEADER" INVENTORY_HEADER INNER JOIN "ORSOSTEST"."ORSOSTEST"."PREFERENCE_DETAIL" PREFERENCE_DETAIL ON
       INVENTORY_HEADER."SYSTEM_ID" = PREFERENCE_DETAIL."RESOURCE_ID"}
GROUP BY PREFERENCE_DETAIL."RESOURCE_ID"
ORDER BY
   count(preference_detail."RESOURCE_ID") ASC

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.