johnnyg123
asked on
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
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
ASKER
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???
That query is awesome ... even includes the name type!
Is there an easy way to exclude the aka name rows that have null values???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Exactly what I was looking for .... Thanks Scott!!!
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