DISTINCT UNION ALL query help (employees and dependents)

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:
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

re: DISTINCT UNION ALL query help (employees and dependents)

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

re: DISTINCT UNION ALL query help (employees and dependents)

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

re: DISTINCT UNION ALL query help (employees and dependents)

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

re: DISTINCT UNION ALL query help (employees and dependents)

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

re: DISTINCT UNION ALL query help (employees and dependents)

Good to hear Zach, especially that you were able to resolve it by re-investigating the problem.