Posted by Hugo Kornelis on 05/03/07 22:45

On Thu, 03 May 2007 20:12:39 GMT, Filips Benoit wrote:

>Dear All,
>
>
>To return the recordsset I wanted I had to use 3 view OR IS THERE A BETTER
>SOLUTION ( 1 view, 1 SP ) ?????????

Hi Filips,

Below are some possibilities, but I couldn't test either of them. It's
much easier to post good answers if you use CREATE TABLE statements to
describe the table instead of just a column list, and INSERT statemenst
for the data instead of a printed table.

The easiest alternative is to just enclose the definitions of the first
two views in the third one:

SELECT a1.ORD_CLIENT_CODE, a1.ORD_CREATION_DATE,
a1.ORD_REQUESTED_QUANTITY, a2.INV_CODE, a2.INV_INVT_ID
FROM (SELECT ORD_ID, ORD_CLIENT_CODE, ORD_CREATION_DATE,
ORD_REQUESTED_QUANTITY
FROM dbo.ORDER
WHERE ORD_PHTI_ID = 17
AND ORD_REQUESTED_DELIVERY_DATE > '2006-01-01') AS a1
LEFT JOIN (SELECT iopm.INVOPM_OP_ORD_ID, i.INV_CODE, i.INV_INVT_ID
FROM dbo.INVOICE AS i
INNER JOIN dbo.INVOICE_ORDER_PHASE_MAP AS iopm
ON i.INV_ID = iopm.INVOPM_INV_ID
WHERE i.INV_INVT_ID = 4) AS a2
ON a1.ORD_ID = a2.INVOPM_OP_ORD_ID;

The second alternative is a quite straight derivation from the one
above. The unusual order of the joins (clarified with parentheses) makes
it a bit hard to grasp

SELECT o.ORD_CLIENT_CODE, o.ORD_CREATION_DATE,
o.ORD_REQUESTED_QUANTITY, a2.INV_CODE, a2.INV_INVT_ID
FROM dbo.ORDER AS o
LEFT JOIN ( dbo.INVOICE_ORDER_PHASE_MAP AS iopm
INNER JOIN dbo.INVOICE AS i
ON i.INV_ID = iopm.INVOPM_INV_ID)
ON iopm.INVOPM_OP_ORD_ID = o.ORD_ID
AND i.INV_INVT_ID = 4
WHERE o.ORD_PHTI_ID = 17
AND o.ORD_REQUESTED_DELIVERY_DATE > '2006-01-01';

The third alternative is a shot in the dark - depending on the exact
table structure, constraints, and your data, this might or might not be
result in the same output as the original query:

SELECT o.ORD_CLIENT_CODE, o.ORD_CREATION_DATE,
o.ORD_REQUESTED_QUANTITY, a2.INV_CODE, a2.INV_INVT_ID
FROM dbo.ORDER AS o
LEFT JOIN dbo.INVOICE_ORDER_PHASE_MAP AS iopm
ON iopm.INVOPM_OP_ORD_ID = o.ORD_ID
LEFT JOIN dbo.INVOICE AS i
ON i.INV_ID = iopm.INVOPM_INV_ID
AND i.INV_INVT_ID = 4
WHERE o.ORD_PHTI_ID = 17
AND o.ORD_REQUESTED_DELIVERY_DATE > '2006-01-01';

And finally, here's the fourth alternative that uses a standard trick to
change an awkward LEFT JOIN in a simple RIGHT JOIN:

SELECT o.ORD_CLIENT_CODE, o.ORD_CREATION_DATE,
o.ORD_REQUESTED_QUANTITY, a2.INV_CODE, a2.INV_INVT_ID
FROM dbo.INVOICE AS i
INNER JOIN dbo.INVOICE_ORDER_PHASE_MAP AS iopm
ON iopm.INVOPM_INV_ID = i.INV_ID
RIGHT JOIN dbo.ORDER AS o
ON o.ORD_ID = iopm.INVOPM_OP_ORD_ID
AND i.INV_INVT_ID = 4
WHERE o.ORD_PHTI_ID = 17
AND o.ORD_REQUESTED_DELIVERY_DATE > '2006-01-01';

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]