?
Solved

Complex sql query help

Posted on 2014-09-22
4
Medium Priority
?
180 Views
Last Modified: 2014-09-22
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
Comment
Question by:johnnyg123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40336772
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
 

Author Comment

by:johnnyg123
ID: 40336983
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40337040
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
 

Author Closing Comment

by:johnnyg123
ID: 40337249
Exactly what I was looking for .... Thanks Scott!!!
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question