Joining to temp table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Joining to temp table

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2009-07-08 : 11:12:44

Hello,

I created a temp table (@TEMP_HIVUSERS) of person_id's that I want to limit a bunch of other queries to only the person_id's available in the temp table and I am having trouble coming up with the correct syntax to join to it. Can anyone give me some direction? Thanks.

DECLARE @TEMP_HIVUSERS TABLE
(
[person_id] uniqueidentifier NOT NULL
)
BEGIN
INSERT INTO @TEMP_HIVUSERS
SELECT distinct person_id
from hiv_users
where assesscode = '042'

DECLARE @TEMP_HIVFLOWSHEET_USERS TABLE
(
[person_id] uniqueidentifier NOT NULL,
[dt_comp_lab_cbc] varchar(10) NULL,
[dt_comp_lab_cd4] varchar(10) NULL,
[dt_comp_lab_cd4p] varchar(10) NULL,
[dt_comp_lab_cmp] varchar(10) NULL,
[dt_comp_lab_creatinine] varchar(10) NULL,
[dt_comp_lab_g6pd] varchar(10) NULL,
[dt_comp_lab_gfr] varchar(10) NULL ,
[dt_comp_lab_hepa_ab] varchar(10) NULL,
[dt_comp_lab_ppd] varchar(10) NULL,
[dt_comp_lab_hepbcab] varchar(10) NULL,
[dt_comp_lab_hepbsab] varchar(10) NULL,
[dt_comp_lab_hepbsag] varchar(10) NULL,
[dt_comp_lab_hepc_ab] varchar(10) NULL,
[dt_comp_lab_hiv_rna] varchar(10) NULL,
[dt_comp_lab_toxoplasm] varchar(10) NULL,
[dt_comp_lab_ua] varchar(10) NULL,
[dt_comp_lab_varicella] varchar(10) NULL,
[dt_comp_lab_gc] varchar(10) NULL,
[dt_comp_lab_rpr] varchar(10) NULL
)

select p.first_name,p.last_name,
cast(p.date_of_birth as datetime) as PtDOB,

BEGIN
INSERT INTO @TEMP_HIVFLOWSHEET_USERS
SELECT
person_id uniqueidentifier,
dt_comp_lab_cbc as LastCBCDate,
dt_comp_lab_cd4 as LastCD4Date,
dt_comp_lab_cd4p as LastCD4PDate,
dt_comp_lab_cmp as LastCMPDate,
dt_comp_lab_creatinine as LastCreatinineDate,
dt_comp_lab_g6pd as LastHSVIIABDate,
dt_comp_lab_gfr as LastGFRDate,
dt_comp_lab_hepa_ab as LastHepAabDate,
dt_comp_lab_ppd as LastPPDDate,
dt_comp_lab_hepbcab as LastHepBcAbDate,
dt_comp_lab_hepbsab as LastHepBsAbDate,
dt_comp_lab_hepbsag as LastHepBsAgDate,
dt_comp_lab_hepc_ab as LastHepCDate,
dt_comp_lab_hiv_rna as LastHIVRnaDate,
dt_comp_lab_toxoplasm as LastToxoplasmDate,
dt_comp_lab_ua as LastUADate,
dt_comp_lab_varicella as LastVaricellaDate,
dt_comp_lab_gc as LastGCDate,
dt_comp_lab_rpr as LastRPRDate

from person p
join temp_hivusers hiv1
join (select h.*
from hiv_flowsheet_ h

inner join (select person_id, max(create_timestamp) as LastHIVFlowEntry
from hiv_flowsheet_
group by person_id) h1
on h1.person_id=h.person_id
and h1.LastHIVFlowEntry=h.create_timestamp
)t
on hiv1.person_id=p.person_id

I keep getting an error that says error near person_id and it brings me to the last line of code.

Thanks in Advance!
Sherri

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 11:19:19

[code]from person p
join @temp_hivusers hiv1
join (select h.*[/code]

KH
[spoiler]Time is always against us[/spoiler]

sross81
Posting Yak Master

228 Posts

Posted - 2009-07-08 : 11:21:20

Oopps yeah I forgot that "@" sign. I still get the same error with that though?

quote:
Originally posted by khtan

from person p
join @temp_hivusers hiv1
join (select h.*

KH
[spoiler]Time is always against us[/spoiler]

Thanks in Advance!
Sherri

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 11:33:12

[code]
from person p
join @temp_hivusers hiv1 ON ??? = ???
join
(
select h.*
from hiv_flowsheet_ h
inner join
(
select person_id, max(create_timestamp) as LastHIVFlowEntry
from hiv_flowsheet_
group by person_id
) h1 on h1.person_id=h.person_id
and h1.LastHIVFlowEntry=h.create_timestamp
)t on hiv1.person_id=p.person_id
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sross81
Posting Yak Master

228 Posts

Posted - 2009-07-08 : 11:37:24

I am still getting an error that says Incorrect Syntax near 'person_id'
Msg 102, Level 15, State 1, Line 73
Incorrect syntax near 'person_id'

When I double click it always brings me to the last line of code but for the life of me I can't see what is wrong with it??

DECLARE @TEMP_HIVUSERS TABLE
(
[person_id] uniqueidentifier NOT NULL
)
BEGIN
INSERT INTO @TEMP_HIVUSERS
SELECT distinct person_id
from hiv_users
where assesscode = '042'

DECLARE @TEMP_HIVFLOWSHEET_USERS TABLE
(
[person_id] uniqueidentifier NOT NULL,
[dt_comp_lab_cbc] varchar(10) NULL,
[dt_comp_lab_cd4] varchar(10) NULL,
[dt_comp_lab_cd4p] varchar(10) NULL,
[dt_comp_lab_cmp] varchar(10) NULL,
[dt_comp_lab_creatinine] varchar(10) NULL,
[dt_comp_lab_g6pd] varchar(10) NULL,
[dt_comp_lab_gfr] varchar(10) NULL ,
[dt_comp_lab_hepa_ab] varchar(10) NULL,
[dt_comp_lab_ppd] varchar(10) NULL,
[dt_comp_lab_hepbcab] varchar(10) NULL,
[dt_comp_lab_hepbsab] varchar(10) NULL,
[dt_comp_lab_hepbsag] varchar(10) NULL,
[dt_comp_lab_hepc_ab] varchar(10) NULL,
[dt_comp_lab_hiv_rna] varchar(10) NULL,
[dt_comp_lab_toxoplasm] varchar(10) NULL,
[dt_comp_lab_ua] varchar(10) NULL,
[dt_comp_lab_varicella] varchar(10) NULL,
[dt_comp_lab_gc] varchar(10) NULL,
[dt_comp_lab_rpr] varchar(10) NULL
)

BEGIN
INSERT INTO @TEMP_HIVFLOWSHEET_USERS
SELECT
t.person_id,
t.dt_comp_lab_cbc as LastCBCDate,
t.dt_comp_lab_cd4 as LastCD4Date,
t.dt_comp_lab_cd4p as LastCD4PDate,
t.dt_comp_lab_cmp as LastCMPDate,
t.dt_comp_lab_creatinine as LastCreatinineDate,
t.dt_comp_lab_g6pd as LastHSVIIABDate,
t.dt_comp_lab_gfr as LastGFRDate,
t.dt_comp_lab_hepa_ab as LastHepAabDate,
t.dt_comp_lab_ppd as LastPPDDate,
t.dt_comp_lab_hepbcab as LastHepBcAbDate,
t.dt_comp_lab_hepbsab as LastHepBsAbDate,
t.dt_comp_lab_hepbsag as LastHepBsAgDate,
t.dt_comp_lab_hepc_ab as LastHepCDate,
t.dt_comp_lab_hiv_rna as LastHIVRnaDate,
t.dt_comp_lab_toxoplasm as LastToxoplasmDate,
t.dt_comp_lab_ua as LastUADate,
t.dt_comp_lab_varicella as LastVaricellaDate,
t.dt_comp_lab_gc as LastGCDate,
t.dt_comp_lab_rpr as LastRPRDate
from person p
join @temp_hivusers hiv1 on hiv1.person_id = p.person_id
join (select h.*
from hiv_flowsheet_ h
inner join (select person_id, max(create_timestamp) as LastHIVFlowEntry
from hiv_flowsheet_
group by person_id) h1
on h1.person_id=h.person_id
and h1.LastHIVFlowEntry=h.create_timestamp
)t
on t.person_id=p.person_id

quote:

Originally posted by khtan


from 	person p
	join @temp_hivusers hiv1 ON ??? = ???
	join 
	(
		select 	h.*
		from 	hiv_flowsheet_ h
			inner join 
			(
				select 	person_id, max(create_timestamp) as LastHIVFlowEntry
				from 	hiv_flowsheet_
				group by person_id
			) h1	on 	h1.person_id=h.person_id
				and 	h1.LastHIVFlowEntry=h.create_timestamp
	)t	on 	hiv1.person_id=p.person_id