By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

DISTINCT UNION ALL query help (employees and dependents)

zachster17
P: 30
Hi everyone,

First of all, sorry for the massive amount of SQL I am about to type. I have a database that has a "lives" table of insured employees and then another table (that links to the lives table) that has all the dependents of the employees. I have also have an alternate ID table in case an employee has more than 1 id.

I have to export all the data into a fixed-text-length file with very combination of the above: meaning all lives, all dependents, and all lives and dependents with the alternate ids as well.

My problem is, that when I run this query I get multiple listings over and over again. It seems that for every dependent that is in the recordset, it duplicates their record as many times as there are other dependents (b/c of the shared insured id). Is ther some kind of clause that will get rid of this duplication? I think part of it has to do with that I'm using a transaction ID in the distinct, but I have to use that in order for the program to link back to record which line is in what file (since only 5,000 at a time can be created in each file and I want to know which transaction went to which file).


Here's my query:
Expand|Select|Wrap|Line Numbers
  1.     INSERT INTO @ExportTable(TransactionID, ExternalID, EmployeeID, MemberID,
  2.     LastName, FirstName, MiddleName,
  3.     Gender, BirthDate, Relationship,
  4.     CovEffectiveDate, CovTermDate, Status,
  5.     PhoneNumber, AddressLine1, AddressLine2,
  6.     City, State, ZipCode, ZipCodeExtension)
  7.     -- lives
  8.     SELECT DISTINCT ot.TransactionID, c.ExternalID, li.EmployeeID, li.EmployeeID AS MemberID,
  9.     li.LastName, li.FirstName, li.MiddleName,
  10.     li.Gender, li.DateOfBirth, '01' AS Relationship,
  11.     li.CoverageEffectiveDate, li.CoverageTermDate, li.LiveStatus,
  12.     li.PhoneNumber, li.AddressLine1, li.AddressLine2,
  13.     li.City, li.State, li.ZipCode, li.ZipCodeExtension FROM
  14.     ed_tblOutputTransactions ot
  15.     INNER JOIN ed_tblLivesInsured li ON ot.InsuredID = li.LiveID
  16.     INNER JOIN ed_tblClients c ON c.ClientID = li.ClientID
  17.     WHERE ot.OutputFile IS NULL AND
  18.     ot.LiveType = 'I'
  19.     UNION ALL
  20.     -- lives (alternate ids)
  21.     SELECT DISTINCT ot.TransactionID,c.ExternalID, ai.AlternateID, ai.AlternateID AS MemberID,
  22.     li.LastName, li.FirstName, li.MiddleName,
  23.     li.Gender, li.DateOfBirth, '01' AS Relationship,
  24.     li.CoverageEffectiveDate, li.CoverageTermDate, li.LiveStatus,
  25.     li.PhoneNumber, li.AddressLine1, li.AddressLine2,
  26.     li.City, li.State, li.ZipCode, li.ZipCodeExtension FROM
  27.     ed_tblOutputTransactions ot
  28.     INNER JOIN ed_tblLivesInsured li ON ot.InsuredID = li.LiveID
  29.     INNER JOIN ed_tblLivesInsuredAlternateIDs ai ON li.LiveID = ai.LiveID
  30.     INNER JOIN ed_tblClients c ON c.ClientID = li.ClientID
  31.     WHERE ot.OutputFile IS NULL AND
  32.     ot.LiveType = 'I'
  33.     UNION ALL
  34.     -- dependents
  35.     SELECT DISTINCT ot.TransactionID, c.ExternalID, li.EmployeeID, li.EmployeeID AS MemberID,
  36.     d.LastName, d.FirstName, d.MiddleName,
  37.     d.Gender, d.DateOfBirth, d.Relationship,
  38.     d.CovEffectiveDate, d.CovTermDate, d.LiveStatus,
  39.     li.PhoneNumber, li.AddressLine1, li.AddressLine2,
  40.     li.City, li.State, li.ZipCode, li.ZipCodeExtension FROM
  41.     ed_tblOutputTransactions ot
  42.     INNER JOIN ed_tblLivesInsured li ON ot.InsuredID = li.LiveID
  43.     INNER JOIN ed_tblLivesDependents d ON li.LiveID = d.InsuredID
  44.     INNER JOIN ed_tblClients c ON c.ClientID = li.ClientID
  45.     WHERE ot.OutputFile IS NULL AND
  46.     ot.LiveType = 'D'
  47.     UNION ALL
  48.     -- dependents (alternate ids)
  49.     SELECT DISTINCT ot.TransactionID,c.ExternalID, ai.AlternateID, ai.AlternateID AS MemberID,
  50.     d.LastName, d.FirstName, d.MiddleName,
  51.     d.Gender, d.DateOfBirth, d.Relationship,
  52.     d.CovEffectiveDate, d.CovTermDate, d.LiveStatus,
  53.     li.PhoneNumber, li.AddressLine1, li.AddressLine2,
  54.     li.City, li.State, li.ZipCode, li.ZipCodeExtension FROM
  55.     ed_tblOutputTransactions ot
  56.     INNER JOIN ed_tblLivesInsured li ON ot.InsuredID = li.LiveID
  57.     INNER JOIN ed_tblLivesInsuredAlternateIDs ai ON ai.LiveID = li.LiveID
  58.     INNER JOIN ed_tblLivesDependents d ON li.LiveID = d.InsuredID
  59.     INNER JOIN ed_tblClients c ON c.ClientID = li.ClientID
  60.     WHERE ot.OutputFile IS NULL AND
  61.     ot.LiveType = 'D'
  62.  
Let me know if I can clarify anything; I dont even understand it myself.

The problem only seems to arise with the depenent parts. For example, I get this (names changed):

TransactionID LastName FirstName
33083 SMITH JOE
33083 SMITH SARAH
33083 SMITH MO
33084 SMITH JOE
33084 SMITH SARAH
33084 SMITH MO
33085 SMITH JOE
33085 SMITH SARAH
33085 SMITH MO
33086 SMITH JOE
33086 SMITH SARAH
33086 SMITH MO
33087 SMITH JOE
33087 SMITH SARAH
33087 SMITH MO


But in reality, the 'transaction' table looks like this:

TransactionID;LiveID;LastName;FirstName;Relationsh ip
33083 16077 JONES JOHN 2
33084 16077 SMITH MO 3
33085 16077 SMITH JOE 3
33086 16077 SMITH SARAH 3
33087 16077 JONES CODY 3

I have no idea why it pulled in the Jones. I run a script before I run this to cleanup the output transaction log to get rid of any duplicates. I'm pretty sure it's the query and the way the relations work between the independents and dependents, but I have no idea how to solve it.

Thank you very much!

Zach
Jun 8 '08 #1
Share this Question
Share on Google+
5 Replies


zachster17
P: 30
Ok, here's some stuff I forgot:

The Client table I link to maps the companies of the employees and the dependents. I link to that in order to get the ExternalID which is how the vendor where I submit the file maps the information.

The output transaction log looks like this:

TransactionID;LiveType;InsuredID;DepLastName;DepFi rstName;Relationship;OutputFile

Once the file is created, the program links back (using the transactions id selected in the query) and fills it with the correct outputfile(only 5,000 per file) that is then loaded
Jun 8 '08 #2

ck9663
Expert 2.5K+
P: 2,878
It would be easier for me if you could post sample (maybe 10 rows each) data of your table. And how do you want your result to be.

-- CK
Jun 9 '08 #3

Delerna
Expert 100+
P: 1,134
In the results that you show in your post, it appears to me that your queries are matching each person in one table to every transaction in another table.
This suggests to me that you need extra join condition to match the transaction records to the correct person.
I can't confidently determine which ones from your post but I will guess that
ed_tblClients needs to join somehow with ed_tblOutputTransactions.

It seems to me that each record in tblClients is matching to every record in tblOutputTransactions.

I agree with ck, 10 rows from each table. Names changed of course
Jun 9 '08 #4

zachster17
P: 30
thanks Delerna and ck,

I took your advice and tried to figure out again if there was a missing join condition. It turns out that, for the 2 dependent additions, joining to the dependents first and the lives second fixed the problem. I guess joining to the lives first was automatically adding the insured for each dependent, therefore the multiple records? I have no idea but it seemed to get rid of the duplicates---these joins are so confusing!

Thanks,

Zach
Jun 10 '08 #5

Delerna
Expert 100+
P: 1,134
Good to hear Zach, especially that you were able to resolve it by re-investigating the problem.
Jun 10 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.