views:

326

answers:

1

I have created a single table in my primary DB called tblGlobalIDMapping that will assign a GUID to all entries and store the other 3 primary ID's across the App to provide for a single ID repository.

The new Table is in the following structure -->

AppGlobalID     uniqueidentifier     NoNulls
PersonID        int                  NoNulls
DRIMaster       int                  Nulls
DRIClient       int                  Nulls

The information I need is in a table in a different DB (same server) with the following partial structure -->

PersonID                int             NoNulls
ApplicationID           tinyint         NoNulls
PersonApplicationID     varchar(14)     NoNulls

A person(ID# 13579) is represented in the second table 1 for each Application. The ApplicationID column stores a number, 1 for DRIMaster and 6 for DRIClient, that tells you what the ID# in PersonApplicationID is for.

I need to do a Bulk Copy/Insert from that table to my new table but I want only 1 row recording the 3 main ID's in the new table.

This is what I have so far but this returns a row for each PersonID.

SELECT PersonID, 
 CASE
  WHEN ApplicationID = 1
  THEN PersonApplicationID
 END AS 'DRIMaster', 
 CASE
  WHEN ApplicationID = 6
  THEN PersonApplicationID
 END AS 'DRIClient'
FROM tblApplicationAssociation
WHERE ApplicationID IN (1,6)

This returns the below -->

PersonID  DRIMaster   DRIClient
_______________________________
108574    71163       NULL
108574    NULL       71163
+2  A: 

Aggregates ignore nulls, so....

SELECT PersonID, 
       Min(CASE
                WHEN ApplicationID = 1
                THEN PersonApplicationID
        END) AS 'DRIMaster', 
       Min(CASE
                WHEN ApplicationID = 6
                THEN PersonApplicationID
        END) AS 'DRIClient'
FROM tblApplicationAssociation
WHERE ApplicationID IN (1,6)
Group By PersonId
G Mastros
This seems to work. I am a little concerned that a DISTINCT PersonID query returns 51241 and this query returns 51233 and I can't currently explain the missing 8 PersonID's.
Refracted Paladin
You are filtering on ApplicationId In (1,6). Is is possible that the 8 missing people don't have those two application id's?
G Mastros
That was my original thought but the following query --> `SELECT DISTINCT PersonIDFROM tblApplicationAssociationWHERE ApplicationID NOT IN (1,6)` Returns 13253 records....I am stumped!
Refracted Paladin
I got it, The above query is idiotic on my part and drastically over simplified. The following query returns the 8 culprits --> `SELECT DISTINCT PersonIDFROM tblApplicationAssociationWHERE PersonID NOT IN (SELECT DISTINCT PersonIDFROM tblApplicationAssociationWHERE ApplicationID IN (1,6))`
Refracted Paladin