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: -
INSERT INTO @ExportTable(TransactionID, ExternalID, EmployeeID, MemberID,
-
LastName, FirstName, MiddleName,
-
Gender, BirthDate, Relationship,
-
CovEffectiveDate, CovTermDate, Status,
-
PhoneNumber, AddressLine1, AddressLine2,
-
City, State, ZipCode, ZipCodeExtension)
-
-- lives
-
SELECT DISTINCT ot.TransactionID, c.ExternalID, li.EmployeeID, li.EmployeeID AS MemberID,
-
li.LastName, li.FirstName, li.MiddleName,
-
li.Gender, li.DateOfBirth, '01' AS Relationship,
-
li.CoverageEffectiveDate, li.CoverageTermDate, li.LiveStatus,
-
li.PhoneNumber, li.AddressLine1, li.AddressLine2,
-
li.City, li.State, li.ZipCode, li.ZipCodeExtension FROM
-
ed_tblOutputTransactions ot
-
INNER JOIN ed_tblLivesInsured li ON ot.InsuredID = li.LiveID
-
INNER JOIN ed_tblClients c ON c.ClientID = li.ClientID
-
WHERE ot.OutputFile IS NULL AND
-
ot.LiveType = 'I'
-
UNION ALL
-
-- lives (alternate ids)
-
SELECT DISTINCT ot.TransactionID,c.ExternalID, ai.AlternateID, ai.AlternateID AS MemberID,
-
li.LastName, li.FirstName, li.MiddleName,
-
li.Gender, li.DateOfBirth, '01' AS Relationship,
-
li.CoverageEffectiveDate, li.CoverageTermDate, li.LiveStatus,
-
li.PhoneNumber, li.AddressLine1, li.AddressLine2,
-
li.City, li.State, li.ZipCode, li.ZipCodeExtension FROM
-
ed_tblOutputTransactions ot
-
INNER JOIN ed_tblLivesInsured li ON ot.InsuredID = li.LiveID
-
INNER JOIN ed_tblLivesInsuredAlternateIDs ai ON li.LiveID = ai.LiveID
-
INNER JOIN ed_tblClients c ON c.ClientID = li.ClientID
-
WHERE ot.OutputFile IS NULL AND
-
ot.LiveType = 'I'
-
UNION ALL
-
-- dependents
-
SELECT DISTINCT ot.TransactionID, c.ExternalID, li.EmployeeID, li.EmployeeID AS MemberID,
-
d.LastName, d.FirstName, d.MiddleName,
-
d.Gender, d.DateOfBirth, d.Relationship,
-
d.CovEffectiveDate, d.CovTermDate, d.LiveStatus,
-
li.PhoneNumber, li.AddressLine1, li.AddressLine2,
-
li.City, li.State, li.ZipCode, li.ZipCodeExtension FROM
-
ed_tblOutputTransactions ot
-
INNER JOIN ed_tblLivesInsured li ON ot.InsuredID = li.LiveID
-
INNER JOIN ed_tblLivesDependents d ON li.LiveID = d.InsuredID
-
INNER JOIN ed_tblClients c ON c.ClientID = li.ClientID
-
WHERE ot.OutputFile IS NULL AND
-
ot.LiveType = 'D'
-
UNION ALL
-
-- dependents (alternate ids)
-
SELECT DISTINCT ot.TransactionID,c.ExternalID, ai.AlternateID, ai.AlternateID AS MemberID,
-
d.LastName, d.FirstName, d.MiddleName,
-
d.Gender, d.DateOfBirth, d.Relationship,
-
d.CovEffectiveDate, d.CovTermDate, d.LiveStatus,
-
li.PhoneNumber, li.AddressLine1, li.AddressLine2,
-
li.City, li.State, li.ZipCode, li.ZipCodeExtension FROM
-
ed_tblOutputTransactions ot
-
INNER JOIN ed_tblLivesInsured li ON ot.InsuredID = li.LiveID
-
INNER JOIN ed_tblLivesInsuredAlternateIDs ai ON ai.LiveID = li.LiveID
-
INNER JOIN ed_tblLivesDependents d ON li.LiveID = d.InsuredID
-
INNER JOIN ed_tblClients c ON c.ClientID = li.ClientID
-
WHERE ot.OutputFile IS NULL AND
-
ot.LiveType = 'D'
-
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
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
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
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
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
Good to hear Zach, especially that you were able to resolve it by re-investigating the problem.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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"
...
|
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 ...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
| |