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

John

That is nothing like iSeries SQL syntax - and you mention Microsoft SQL (Server) - btw, SQL does not belong to M$, it is a technology, not a product! (Rant over)

You might find more help on the PC list here. And there is always msdn at microsoft.com (MS Developer's Network) that should have syntax for SQL Server statements somewhere.

And unless "oj" is a special operator in Transact-SQL (is that the right name for SQL Server SQL?), you really need an exception join to get things that are in the first table and not in the second.

OK - googled it - google is your friend - oj appears to means "outer join" and seems related to ODBC, not SQL as such. Here is a link that has some info - http://msdn.microsoft.com/en-us/library/ms710299(VS.85).aspx

Good luck

jmmckee 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