473,287 Members | 1,827 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

DISTINCT UNION ALL query help (employees and dependents)

zachster17
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
5 2492
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
2,878 Expert 2GB
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
1,134 Expert 1GB
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
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
1,134 Expert 1GB
Good to hear Zach, especially that you were able to resolve it by re-investigating the problem.
Jun 10 '08 #6

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

Similar topics

2
by: mfyahya | last post by:
I have two tables, both containing an 'authors' column. Is there a way to get a unique list of authors from the two tables? I tried SELECT DISTINCT `authors` from `table1`, `table2`; but I got an...
5
by: Chris Kettenbach | last post by:
Good Morning, Sorry for xposting. Just need a liitle help. I have an xml file that's generated from a database. How do I select distinct values from a field in xslt and then loop through the...
9
by: Kelvin | last post by:
Okay so this is baking my noodle. I want to select all the attritbutes/fields from a table but then to excluded any row in which a single attributes data has been duplicated. I.E. Here's my...
5
by: Fred Zuckerman | last post by:
Can someone explain the difference between these 2 queries? "Select Distinct id, account, lastname, firstname from table1" and "Select DistinctRow id, account, lastname, firstname from table1" ...
2
by: kevcar40 | last post by:
i have a query which is the result of 4 queries being joined query 1 reason count_of_reason query 2 reason count_of_reason query 3 reason count_of_reason query 4 reason ...
2
by: Kezwana | last post by:
Hi, I am a intermediate user who has a database of employees and their training history/courses attended. Can someone please help - I need to find out all the employees who HAVEN'T done a particular...
6
by: plaster1 | last post by:
Been trying to come up with a query to filter-down my sample set into distinct records. For instance, lets say column1 is a sample set, column2 is the parameter, and column3 is a name and column4...
7
by: jb1 | last post by:
Hello All, I am trying to create a DTS package. I have two tables tbl_A and tbl_B with similar data/rows but no primary keys. tbl_A is master. I would like this package to query tbl_A and...
0
by: =?Utf-8?B?anAybXNmdA==?= | last post by:
After I have filled my DataSet with multiple DataTables, how would I make a call that can produce distinct results without having to make a direct SQL call? I don't even understand that...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.