NON ANSI VS ANSI Queries. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NON ANSI VS ANSI Queries.

Author

Topic

raj_marthi
Starting Member

1 Post

Posted - 2011-02-04 : 21:35:58

I am updating a SQL query from old Non Ansi joins (*=) to LEFT Outer joins. It works fine Until the ORGANIZATION_ID filter. After that if the value XPLI.PRICE_LIST_ID = 8 is present in the XPLI table then both queries give the same result and work fine.

But if there is XPLI.PRICE_LIST_ID !=8 but something different for that INVENTORY_ITEM_ID Then in the first case it returns a NULL value and in the second case it gives nothing empty row in the result. I want the NULL in the second case too. Please help!

SELECT
XPLI.UNIT_COST_AMOUNT,
XPL.CHILD_LIST_ID
FROM
XXCG_PRICE_LINES_EXPORT_V XPLI,
XXCG_PRICE_LISTS_EXPORT_V XPL
WHERE

XPL.PRICE_LIST_ID *= XPLI.PRICE_LIST_ID AND
XPL.PRICE_LIST_ID = 8 AND
XPLI.ORGANIZATION_ID = 150 AND
XPLI.INVENTORY_ITEM_ID = 575424

--------------------------------------------------------------------

SELECT
XPLI.UNIT_COST_AMOUNT,
XPL.CHILD_LIST_ID
FROM
XXCG_PRICE_LISTS_EXPORT_V XPL
LEFT OUTER JOIN XXCG_PRICE_LINES_EXPORT_V XPLI
ON XPL.PRICE_LIST_ID = XPLI.PRICE_LIST_ID

WHERE XPLI.PRICE_LIST_ID = 8 AND
XPLI.ORGANIZATION_ID = 150 AND
XPLI.INVENTORY_ITEM_ID = 575424

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-04 : 23:02:30

WHERE (XPLI.PRICE_LIST_ID = 8 AND
XPLI.ORGANIZATION_ID = 150 AND
(XPLI.INVENTORY_ITEM_ID = 57542 OR XPLI.INVENTORY_ITEM)ID is null)

1st query returns all records from both tables and filters results

2nd query filters all recodes based on the price list id join, then filters those results to only matching conditions. Since the NULL value does not match the filter conditions, it is not returned.

good reading at madhi's blog: http://beyondrelational.com/blogs/naomi/archive/2010/03/22/why-left-join-doesn-t-bring-all-records-from-the-left-table.aspx

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources