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'
GoldenJagAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

folderolCommented:
...
LEFT OUTER JOIN
CARDH_Enrollment
left outer join
(
select CARDH_Enrollment.[Primary Key] as PRIMARY_KEY, RegistrationType from CARDH_Enrollment where RegistrationType = 'E'
) as N_holders on CARDH_Enrollment.[Primary Key] = N_holders.PRIMARY_KEY and CARDH_Enrollment.RegistrationType = 'N'

   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 = 'N' and N_holders.RegistrationType is not null) OR
            (CARDH_Enrollment.RegistrationType = 'E'))
...

I simply found all the holders with the registration type E first then the OR later on in the query allows me to process CARDH_Enrollment as two sets (set of all registrations 'N' with an 'E' also and set of all registrations 'E' only). This assumes we don't want holders with registration 'N' if they don't also have an 'E'.

If you want all 'N' registrations and 'E' registrations but only the 'N' if both exist, you just swap the ='N' for ='E' in the virtual table N_holders, and in the OR clause, then change the is not null to is null.

I think :)

Tom.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GoldenJagAuthor Commented:
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
      )
folderolCommented:
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


Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

GoldenJagAuthor Commented:
OK Tom, Thanks!!
folderolCommented:
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

GoldenJagAuthor Commented:
Tom, I am still working on this.  I got side tracked.
folderolCommented:
Okay, I know how that is :)
GoldenJagAuthor Commented:
Tom, i will be inquiring on this question today or tomorrow.  Sorry for any inconvenience.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.