Go Premium for a chance to win a PS4. Enter to Win

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

Complex sql query help

Here is my situation

I have a table named Names with the following format

id int
primaryfname  varchar(250)
Primarylname   varchar(250)
akafname          varchar(250)
akalname           varchar(250)
Birthdate                 varchar(50)

Here is some sample data

ID            PrimaryFName     PrimaryLname        AkaFname          AkaLname     BirthDate    
6927      John                      Doe                           Jim                       Jones              25 Aug 1972
6927      John                      Doe                           Jim                       Doe                25 Dec 1974
7000       Jane                         Doe                                                                               10 jan   1956


I am trying to populate another table named NameList with the following format
id int
fname  varchar(250)
lname   varchar(250)
Birthdate    varchar(50)

Here is the criteria for the records should be written to the NameList Table

For a given id in the Names table, there should be

 one record for each  PrimaryFName   and   PrimaryLname    combination

if the birth date is different for the PrimaryFName   and   PrimaryLname    combination there should be a record for each different birth date

Like wise, there should be one entry for each akaFName   and   akaLname    combination associated with the PrimaryFName   and   PrimaryLname    combination.  (if there is a different birth date then another record written as well)


using the example above the namelist table should have the following records

ID            FName     Lname       BirthDate    
6927      John        Doe            25 Aug 1972
6927      John        Doe            25 Dec 1974
6927      Jim             Jones         25 Aug 1972
6927      Jim                Jones          25 Dec 1974
6927      Jim            Doe             25 Aug 1972
6927      Jim                Doe             25 Dec 1974
7000       Jane          Doe              10 jan   1956
0
johnnyg123
Asked:
johnnyg123
  • 2
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
SELECT DISTINCT
    ID,
    CASE WHEN whichName = 'Primary' THEN primaryfname ELSE akafname END AS FName,
    CASE WHEN whichName = 'Primary' THEN primarylname ELSE akalname END AS LName,
    Birthdate
FROM names
CROSS JOIN (
    SELECT 'Primary' AS whichName UNION ALL
    SELECT 'aka'
) AS whichNames
0
 
johnnyg123Author Commented:
Scott,
That query is awesome ... even includes the name type!

Is there an easy way to exclude the aka name rows that have null values???
0
 
Scott PletcherSenior DBACommented:
Sure:

SELECT DISTINCT
     ID,
     CASE WHEN whichName = 'Primary' THEN primaryfname ELSE akafname END AS FName,
     CASE WHEN whichName = 'Primary' THEN primarylname ELSE akalname END AS LName,
     Birthdate
 FROM names
 INNER JOIN (
     SELECT 'Primary' AS whichName UNION ALL
     SELECT 'aka'
 ) AS whichNames ON
    whichNames.whichName = 'Primary' OR
    (akafname IS NOT NULL OR akalname IS NOT NULL)
0
 
johnnyg123Author Commented:
Exactly what I was looking for .... Thanks Scott!!!
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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