[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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