SQL command issue

[mlager]

Hello, I'm using MSSQL 2005. I have a sql query that looks like this (notice the *=):

CREATE VIEW RPS_PAYORGROUPING AS
SELECT A1.BILLNO,A1.MRN,
A1.PAYTYPE AS PAYTYPE1,
A1.PAYOR AS PAYOR1,
A2.PAYTYPE AS PAYTYPE2,
A2.PAYOR AS PAYOR2
FROM BILLED A1, BILLED A2
WHERE A1.BILLNO *= A2.BILLNO AND A1.DESCRIPT = 'INV' AND A2.DESCRIPT = '2ND'

When I create this as a view, MSSQL converts it to:

SELECT A1.BILLNO, A1.MRN, A1.PAYTYPE AS PAYTYPE1, A1.PAYOR AS PAYOR1, A2.PAYTYPE AS PAYTYPE2, A2.PAYOR AS PAYOR2
FROM dbo.BILLED AS A1 LEFT OUTER JOIN
dbo.BILLED AS A2 ON A1.BILLNO = A2.BILLNO
WHERE (A1.DESCRIPT = 'INV') AND (A2.DESCRIPT = '2ND')

The second query doesn't not include the NULL entries as in query 1. How can I get the first query converted so it shows records that don't exist in the second aliased table. If this doesn't make sense, let me know. Thanks for any help!

 

[notadba]

Hi mlager,

Looks like the second criteria on a2.descript is invalidating the outer join. You need to change this to something like:


SELECT A1.BILLNO, A1.MRN, A1.PAYTYPE AS PAYTYPE1, A1.PAYOR AS PAYOR1, A2.PAYTYPE AS PAYTYPE2, A2.PAYOR AS PAYOR2
FROM dbo.BILLED AS A1 LEFT OUTER JOIN
dbo.BILLED AS A2 ON A1.BILLNO = A2.BILLNO
AND A2.DESCRIPT = '2ND'
WHERE (A1.DESCRIPT = 'INV')

...and hopefully that should do it.

 

[mlager]

Fantastic, I appreciate your help on this!