Date: 04/18/05 (SQL Server)    Keywords: no keywords

Okay, I'm at a logjam on this, and I desperately need to get it fixed. My present code is definitely not working-- it's pulling way, way too many records, and I need an answer fast. If any of you happen to have one, I'd greatly appreciate it.

You have three types of tables.

Type 1: the Employees table. It contains a recordnum field, and a number of other, irrelevant for the purposes of the question, fields.
Type 2: the Link table. It contains two columns, erecordnum and drecordnum. "E" stands for emplyees, "D" for dependents.
Type 3: one or more (in my case, six at the moment, eventually twenty-nine) tables, all of which have recordnum fields.

Recordnum is an arbitrary number that goes from one to (in this case) 667,770. The Employees table contains the actual employee records-- for example, my first eight employee records are 2, 4, 5, 7, 8, 12, 14, and 15. Dependents are listed sequentially under the employees, so for employee 2, dependent would be 3. For employee 8, dependents would be 9, 10, and 11. Thus, the link table looks like this for the first bunch of records:

erecordnum drecordnum
2 2
2 3
4 4
5 5
5 6
7 7
8 8
8 9
8 10
8 11
12 12
12 13
14 14
15 15
...etc. (That the employees are showing up as dependents in the link table is intentional for various reasons.)

What I need is a SELECT statement that will pull all the relevant information I need form these tables. The Employees table already has everything I need for the employee records, but I need to add columns for each dependent, so the final table will look like the Link table above (i.e., all the employee information will be repeated for each dependent). The problem I'm having is in figuring out how to write the WHERE clause so that it's pulling the right rows, and only the right rows, from each table. My present SELECT statement:

insert into elig (emembno, efstnam, elstnam, eadrln1, eadrln2, ecitycd, estacod, ezipcod, ebthdat, esexcod,
egrpnum, eeffdat, eexpdat, ecommcd, etiercd, drelcod, dmembno, dfstnam, dlstnam, dbthdat, dsexcod,
deffdat, dexpdat, ehomppg, ecntycd, action, termdate, ephoneno, filler) select
e.emembno, e.efstnam, e.elstnam, e.eadrln1, e.eadrln2, e.ecitycd, e.estacod, e.ezipcod, e.ebthdat,
e.esexcod, e.egrpnum, e.eeffdat, e.eexpdat, e.ecommcd, e.etiercd, ins.relcode, nm1.idcode, nm1.fstnam,
nm1.lstnam, dmg.dtperiod, dmg.sex, d1.dtperiod, d2.dtperiod, space(3), space(2), hd.mainttype,
space(8), space(10), space(10)
from employees e
inner join eligstaging e1 on e.recordnum = e1.erecordnum
left join ins on e.recordnum = e1.erecordnum and ins.recordnum = e1.drecordnum
left join nm1 on e.recordnum = e1.erecordnum and nm1.recordnum = e1.drecordnum
left join dmg on e.recordnum = e1.erecordnum and dmg.recordnum = e1.drecordnum
left join dtp d1 on e.recordnum = e1.erecordnum and d1.recordnum = e1.drecordnum and d1.dtqual = '348'
left join dtp d2 on e.recordnum = e1.erecordnum and d2.recordnum = e1.drecordnum and d2.dtqual = '349'
left join hd on e.recordnum = e1.erecordnum and hd.recordnum = e1.drecordnum
where e.egrpnum <> 'DO NOT LOAD'

This is obviously not working, because instead of getting the 671K records I'm expecting, I'm getting enough records to fill about 100GB worth of temp space, maxing out the system and causing the query to abort. Can someone point out to me what changes I need to make in this statement to pull just the records I need?

Thanks.

Source: http://www.livejournal.com/community/sqlserver/24798.html