temp tablevar and inserting data from multiple tables

[sap1958]

declare @customexport table
(
--first table school_table
schlid char(10),
schlschool char(10),
schldegyr char(4),
--second table school_table (this is where I get stuck)
schlid char(6)

schlschool char(10),
schldegyr char(4)
--third table bio table
spouseID char(10),
)
insert into @customexport
(schlid,schlschool,schldegyr,?????)
select schlid,schlschool,schldegyr,?????
from school_table
left outer join school_table ??
on schlid=schlid

The question is how do I declare and insert data into multiple instances of the same table. In this case I want to get the primary persons school and another instance of the same table to get the spouse school. Essentially if the spouseID is equal to the second instance of the school then I want to include it

 

[gmmastros]

I assume there is something in the bio table that identifies the school that the spouse attends. Is this correct? To make things easier on everyone, can you show the relevant column names from the bio table.

While we're at it, this is NOT going to work.
[tt][blue]
declare @customexport table
(
--first table school_table
schlid char(10),
schlschool char(10),
schldegyr char(4),
--second table school_table (this is where I get stuck)
schlid char(6)

schlschool char(10),
schldegyr char(4)
--third table bio table
spouseID char(10),
)
[/blue][/tt]

Column names in a table MUST be unique. This applies to real tables, temp tables, global temp tables, and table variables. There is no way around this. Instead, you could modify the column names. Something like this:

    declare  @customexport table
(
--first table    school_table
schlid char(10),            
schlschool char(10),            
schldegyr char(4),
--second table   school_table (this is where I get stuck)
[!]spouse_[/!]schlid char(6),
            
[!]spouse_[/!]schlschool char(10),            
[!]spouse_[/!]schldegyr char(4),
--third table  bio table
spouseID char(10)
)

The next part isn't going to work either. When you write a query that references the same table twice, you MUST give at least one of them an alias.

insert into @customexport
(schlid,schlschool,schldegyr,spouse_schlid, spouse_schlschool, spouse_schldegyr)
select PrimarySchool.schlid,PrimarySchool.schlschool,PrimarySchool.schldegyr,
       SpouseSchool.schlid,SpouseSchool.schlschool,SpouseSchool.schldegyr
from school_table As PrimarySchool
left outer join school_table As SpouseSchool
on PrimarySchool.schlid=SpouseSchool.schlid

Even still... the above query will probably NOT return the data you expect because there is nothing identifying each school as belonging to someone or their spouse.

I suspect the BIO table is the keep to the whole thing.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom