Sybase Sql - Remove "semi-duplicates" From Query Results

I have a query that uses two SELECT statements that are combined using a UNION ALL. Both statements pull data from similar tables to populate the query results. I am attempting to

Solution 1:

Your query does not make sense. I would start by eliminating the implicit cartesian product, generated by the , in the from clause.

My guess is that the from clause should be:

FROM 
    PatReferrals PR LEFTJOIN
    Patient P
    ON PR.PatientID = P.PatientID leftouterjoin
    Rolodex R
    ON P.RolodexID = R.RolodexID leftouterjoin
    PatEligibilities PE
    ON PR.PatientID = PE.PatientID leftouterjoin
    Reimbursors RE
    ON PE.ReimbursorID = RE.ReimbursorID leftouterjoin
    Teams T ON PR.TeamID = T.TeamID

Once you do this, you may not need the union all or the select distinct. You may be able to put both the reimbursors and the eligibilities in the same query.

Solution 2:

Use a subquery or subqueries.

The overall query should be written using the following pattern:

SelectDistinct [Person Data]
 From PersonTable
     leftJointo otherTable1 -- add outer join for each table you need data fromOn [Conditions that ensure join can generate onlyonerowper person,
               ... and specify which of possibly many rowsto get...]

Make sure the conditions eliminate any possibility for the join to generate more than one row from the other [outer] table per person row in in the person table,. This may (and often does) require that the join condition be based on a subquery, as, for example...

SelectDistinct [Person Data]
 From PersonTable p
     leftJointo employments e -- add outer join for each table you need data fromOn e.PersonId = p.PersonId 
            and e.HireDate = (SelectMax(hiredate) from employments
                              where personId = p.PersonId)

Solution 3:

After working with this for quite some time today, I found a solution to the problem that I was having. Here is the solution that works and pulls the correct information that I was needing:

SELECTDISTINCT 
    TeamNum,
    LastName,
    FirstName,
    City,
    ReimbursorName =CASEWHENmax(ReimbursorName) IN ('R1', 'E1')
            THEN'1'WHENmax(ReimbursorName) IN ('R2', 'E2')
            THEN'2'ELSE'3'END,
    PatientID 
FROM
    (
    SELECT
        TeamNum =CASEWHEN T.TeamName ='Alpha Team'THEN'1'WHEN T.TeamName IN ('Bravo Team', 'Charlie Team')
                    THEN'2'WHEN T.TeamName ='Delta Team'THEN'3'ELSE'<Undefined>'END,
        P.PatientLastName AS LastName,
        P.PatientFirstName AS FirstName,
        R.PrimaryCity AS City,
        ReimbursorName =CASEWHEN RE.ReimbursorDescription ='Medicare'Then'R1'WHEN RE.ReimbursorDescription ='Medicaid'Then'R2'ELSE'R3'END,
        P.PatientID AS PatientID
    FROM 
        PatReferrals PR LEFTJOIN Patient P ON PR.PatientID = P.PatientID,
        Patient P LEFTOUTERJOIN Rolodex R ON P.RolodexID = R.RolodexID,
        PatReferrals PR LEFTOUTERJOIN PatReimbursors PRE ON PR.PatientID = PRE.PatientID,
        PatReimbursors PRE LEFTOUTERJOIN Reimbursors RE ON PRE.ReimbursorID = RE.ReimbursorID,
        PatReferrals PR FULLOUTERJOIN Teams T ON PR.TeamID = T.TeamID
    WHERE 
        PR.ReferralDate BETWEEN GETDATE()-4AND GETDATE()-1AND PR.Status <>'R'AND PRE.CoveragePriority ='1'AND PRE.ExpirationDate ISNULLUNIONALLSELECT
        TeamNum =CASEWHEN T.TeamName ='Alpha Team'THEN'1'WHEN T.TeamName IN ('Bravo Team', 'Charlie Team')
                    THEN'2'WHEN T.TeamName ='Delta Team'THEN'3'ELSE'<Undefined>'END,
        P.PatientLastName AS LastName,
        P.PatientFirstName AS FirstName,
        R.PrimaryCity AS City,
        ReimbursorName =CASEWHEN RE.ReimbursorDescription ='Medicare'Then'E1'WHEN RE.ReimbursorDescription ='Medicaid'Then'E2'ELSE'E3'END,
        P.PatientID AS PatientID
    FROM 
        PatReferrals PR LEFTJOIN Patient P ON PR.PatientID = P.PatientID,
        Patient P LEFTOUTERJOIN Rolodex R ON P.RolodexID = R.RolodexID,
        PatReferrals PR LEFTOUTERJOIN PatEligibilities PE ON PR.PatientID = PE.PatientID,
        PatEligibilities PE LEFTOUTERJOIN Reimbursors RE ON PE.ReimbursorID = RE.ReimbursorID,
        PatReferrals PR FULLOUTERJOIN Teams T ON PR.TeamID = T.TeamID
    WHERE 
        PR.ReferralDate BETWEEN GETDATE()-4AND GETDATE()-1AND PR.Status <>'R'AND PE.Status <>'V'AND PE.ApplicationDate BETWEENDATE(PR.ReferralDate)-5ANDDATE('2100/01/01')
    ) 

AS DUMMYTBL
GROUPBY
    TeamNum,
    LastName,
    FirstName,
    City,
    PatientID
ORDERBY 
    DUMMYTBL.LastName ASC,
    DUMMYTBL.FirstName ASC

Thanks for all the responses that were provided.