Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

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'
0
GoldenJag
Asked:
GoldenJag
  • 4
  • 4
1 Solution
 
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.
0
 
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
      )
0
 
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


0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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

0
 
GoldenJagAuthor Commented:
Tom, I am still working on this.  I got side tracked.
0
 
folderolCommented:
Okay, I know how that is :)
0
 
GoldenJagAuthor Commented:
Tom, i will be inquiring on this question today or tomorrow.  Sorry for any inconvenience.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now