asked on

Left Outer Join

Hi, can someone help me. i have a field in the database "CARDH_Enrollment.RegistrationType" it can hold three possible values "E", "N", or "P". Now in some cases a holder can have two of those values. I need to figure out a way that if the holder has two values and they are N and E then i need the value of N to show it instead of the E. can i use a case or if statement in my query?

i have the following query and i am not sure about how to get around the left outer join (which gives me the "E":

SELECT
      :
      :
      :
FROM
      Enrollment
            INNER JOIN
      (
            (CARDH
                        LEFT OUTER JOIN
                        CARDH_Enrollment
                              ON CARDH_Enrollment.AC = CARDH.AC
                              AND CARDH_enrollment.RegistrationFailed = 0
                              And CARDH_enrollment.ProgramID = CARDH.ProgramID
                              AND CARDH.ProgramID = '77'
                              And CARDH_Enrollment.RegistrationType = 'E'
                        )INNER JOIN
                        CardTbl
                              ON CARDH.CARDHID = CardTbl.CARDHID
                              and CARDH.ProgramID = CardTbl.ProgramID
                              AND CARDH.ProgramID = '77'
      )
                  ON CardTbl.EnrollmentID = Enrollment.EnrollmentID
WHERE

      Enrollment.Received = 1
      And Enrollment.Date > '3/29/2006'
      And Enrollment.Date < '3/31/2006'

ASKER CERTIFIED SOLUTION

folderol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GoldenJag

ASKER

folderol,

i am trying to test your code...could you help me with the syntax, i get the followoing error Server: Msg 156, Level 15, State 1, Line 58
Incorrect syntax near the keyword 'ON'. (this is using sql analyzer)

FROM
      Enrollment
            INNER JOIN
      (
                  (
                        CardHolder
                                    LEFT OUTER JOIN
                              CardHolder_Enrollment
                                    LEFT OUTER JOIN
                              (
                                    SELECT
                                          CardHolder_Enrollment.CardHolderTempID AS PRIMARY_KEY,
                                          RegistrationType
                                    FROM
                                          CardHolder_Enrollment
                                    WHERE
                                          RegistrationType = 'E'
                              ) AS N_holders
                                          ON CardHolder_Enrollment.CardHolderTempID = N_holders.PRIMARY_KEY
                                          And CardHolder_Enrollment.RegistrationType = 'N'
                                          ON CardHolder_Enrollment.HMAC = CardHolder.HMAC
                                          And CardHolder_enrollment.RegistrationFailed = 0
                                          And CardHolder_enrollment.ProgramID = CardHolder.ProgramID
                                          And CardHolder.ProgramID = 'PM000077'
                                          And
                              (
                                          (
                                                CardHolder_Enrollment.RegistrationType = 'N'
                                                And N_holders.RegistrationType IS Not NULL
                                          )
                                    Or
                                          (
                                                CardHolder_Enrollment.RegistrationType = 'E'
                                          )
                              )
                  )
-line 58-->                              ON Card_Table.EnrollmentID = Enrollment.EnrollmentID
      )

folderol

I am familiar with what you are attempting (at least what I think you are attempting :),
with the nesting in the table joins, crafting the sequence joins occur allows for more control over how to proceed with nulls. I may have selected the wrong place for my additional code. I will need a couple hours before I can take a good look.

Let us know if you find a fix. Until later,
Tom

GoldenJag

ASKER

OK Tom, Thanks!!

folderol

Your last 3 rows repeated here as:
)
ON Card_Table.EnrollmentID = Enrollment.EnrollmentID
)

should appear like these rows to match your query in the original question.

)INNER JOIN
CardTbl
ON CARDH.CARDHID = CardTbl.CARDHID
and CARDH.ProgramID = CardTbl.ProgramID
AND CARDH.ProgramID = '77'
)

ON Card_Table.EnrollmentID = Enrollment.EnrollmentID
)

You changed the table names a little but the significant idea is that there is no table Card_Table in your query that has the problem with line 58.

I appears to me that you are gathering columns from tables as you go, the tables are inverted in order of reference.
If you need to break this into several distinct queries to solve how it should assemble up to the desired resultset, you could rewrite this as

select Enrollment.yourcol1name, Enrollment.yourcol2name, virtual_table_1.column1, virtual_table_1.column2, ...
from Enrollment
INNER JOIN
(
select [column1, ...]
from
Card_Table
inner join
CardHolder on Card_Table.CARDHID = Cardholder.CARDHID and [etc]
left outer join
Cardholder_Enrollment on Cardholder.AC = Cardholder_Enrollment.AC and [etc]
left outer join
(
[my stuff]
)
as N_holders on CardHolder_Enrollment.CardHolderTempID = N_holders.PRIMARY_KEY
)
as virtual_table_1 on Enrollment.EnrollmentID = virtual_table_1.EnrollmentID

where ...

---------
In the where clause you can put a lot of the conditions that now exist in the JOINS.

This would give you the chance to examine the interim resultsets the code uses. There is nothing inferior with the approach you took so I would only consider the rewrite to assist with debugging if it becomes necessary.

Tom

GoldenJag

ASKER

Tom, I am still working on this. I got side tracked.

folderol

Okay, I know how that is :)

GoldenJag

ASKER

Tom, i will be inquiring on this question today or tomorrow. Sorry for any inconvenience.