problems with temp table variables

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)

problems with temp table variables

Author

Topic

igor92128
Starting Member

23 Posts

Posted - 2009-06-09 : 17:36:57

Hello,

I have a temp table called GUID and I am trying to query data in another table where all those GUIDS exist, and there can be more then one GUID. Here is the code:

declare @GUID table
(
guid_id uniqueidentifier
)

declare @theGUID AS uniqueidentifier
set @theGUID = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'

insert into @GUID (guid_id)
select r_id2 from @sd_r_id //this is another temp table
where r_id2 in (select r_id from main_table (nolock))

select r_id from main_table (nolock)
left join main_table on main_table.R_ID = @GUID.guid_id

I get an error: Must declare the scalar variable "@GUID". Why is it complaining if I am already declaring it as a table?

Any ideas?

Igor

igor92128
Starting Member

23 Posts

Posted - 2009-06-09 : 17:39:33

I made a mistake on the line where I SET the temp variable. It should be:

set @theGUID = @GUID.guid_id

but I still get the same error.

igor92128
Starting Member

23 Posts

Posted - 2009-06-09 : 17:42:14

The last line is actually: left join main_table on main_table.R_ID = @GUID.@theGUID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 17:55:40

select r_id from main_table (nolock)
left join @GUID on ~~@GUID.~~guid_id = main_table.R_ID

E 12°55'05.63"
N 56°04'39.26"

igor92128
Starting Member

23 Posts

Posted - 2009-06-09 : 19:35:00

Ok thanks, one more question ...

how do I join two temp tables if their primary key is an R_ID uniqueidentifier?

I can't seem to get the syntax right and it complains of not declaring scalars.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 19:53:50

select r_id from main_table (nolock)
left join @GUID AS xyz on xyz.guid_id = main_table.R_ID

E 12°55'05.63"
N 56°04'39.26"

igor92128
Starting Member

23 Posts

Posted - 2009-06-09 : 22:42:27

Yes but that only involves one temp table.

lets say I have these two tables

declare @GUID table
(
guid_id uniqueidentifier
first_name varchar(50)
last_name varchar(50)
)
insert into @GUID ...

declare @OTHERGUID table
(
guid_id uniqueidentifier
symptom varchar(50)
duration varchar(50)
)
insert into @OTHERGUID ...

I want to join those two tables on the guid. So my output would be:

"John" "Doe" "Coughing" "3 Days"
...
...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 01:05:05

select *
from @guid as g
inner join @otherguid as w on w.guid_id = g.guid_id

E 12°55'05.63"
N 56°04'39.26"

igor92128
Starting Member

23 Posts

Posted - 2009-06-10 : 12:34:24

This is good but I am getting about 3 times the records that are in the individual tables in my result. If there is one unique record for @GUID it returns one record which is correct but lets say I have this data:

in @GUID
"{someguid}" "John" "Doe"
"{someguid}" "John" "Doe"

in @OTHERGUID
"{someguid}" NULL NULL
"{someguid}" NULL NULL

the result set seems to be:
"{someguid}" "John" "Doe" "{someguid}" NULL NULL
"{someguid}" "John" "Doe" "{someguid}" NULL NULL
"{someguid}" "John" "Doe" "{someguid}" NULL NULL
"{someguid}" "John" "Doe" "{someguid}" NULL NULL

"{someguid}" - is the same GUID for all of the above.

Thanks,
Igor

igor92128
Starting Member

23 Posts

Posted - 2009-06-10 : 12:38:46

If there are 6 records with the same information like above, I get 36 results so it's multiplying.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 12:44:49

Use the DISTINCT, Igor!

E 12°55'05.63"
N 56°04'39.26"

igor92128
Starting Member

23 Posts

Posted - 2009-06-10 : 12:53:43

But then I get about half the records of the tables.

I need to output the duplicates as they are in the tables, and join everything on the GUID. I can tell you that @GUID and @OTHERGUID both have the same amount of rows, and the result should have the same rows, I am just putting the two tables together. I can easily merge the tables in Excel by selecting * from each table individually and sorting by GUIDs and cutting and pasting but I'd like to see how this is done in SQL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-10 : 14:13:46

quote:
Originally posted by igor92128

But then I get about half the records of the tables.

I need to output the duplicates as they are in the tables, and join everything on the GUID. I can tell you that @GUID and @OTHERGUID both have the same amount of rows, and the result should have the same rows, I am just putting the two tables together. I can easily merge the tables in Excel by selecting * from each table individually and sorting by GUIDs and cutting and pasting but I'd like to see how this is done in SQL.

since the guid values are same each record will always match all the records on other table on guid thats why you get 36 (6*6) records in result. what you can try is below


SELECT *
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY GUID_ID ORDER BY GUID_ID) AS Seq,*
FROM @GUID
)t1
JOIN 
(SELECT ROW_NUMBER() OVER (PARTITION BY GUID_ID ORDER BY GUID_ID) AS Seq,*
FROM @OTHERGUID
)t2
ON t2.GUID_ID =t1.GUID_ID 
AND t2.Seq=t1.Seq

igor92128
Starting Member

23 Posts

Posted - 2009-06-10 : 15:19:17

Thank you that worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources