Temporary Tables.

[Work23]

Hello. I have been creating some temporary tables to later do joins with. Upon completing the join of the two virtual tables, I get the records to be repeating continuously. Is there any reason for this or would it be a coding issue? This is the code. Fairly simple to understand. I'm only wondering if this is a logic issue or something that I'm not understanding within the tool it self. Thank you very much for you help! (When I join table #Temp2 and table #Temp3 into #Temp12, I get numerous repeats on the Event_ID)


DROP TABLE #TEMP1
CREATE TABLE #TEMP1

(T1_EVENTID CHAR (20),
T1_TIMESTAMP CHAR (50),
T1_EVENTNAME CHAR (20),
T1_EVENTFIELD CHAR (25),
T1_EVENTVALUE CHAR (50))

INSERT INTO #TEMP1

SELECT EVENT_ID,
EVENT_TIMESTAMP,
EVENT_NAME,
EVENT_PARAMETER,
EVENT_VALUE
FROM DBO.MYTABLE (Created table in Database)
WHERE EVENT_NAME='LOGIN'

SELECT * FROM #TEMP1;
/* End Login*/


/*Begin Login and SESSION_ID*/
DROP TABLE #TEMP2
CREATE TABLE #TEMP2

(T2_EVENTID CHAR (20),
T2_TIMESTAMP CHAR (50),
T2_EVENTNAME CHAR (20),
T2_EVENTFIELD CHAR (25),
T2_EVENTVALUE CHAR (50))


INSERT INTO #TEMP2

SELECT T1_EVENTID,
T1_TIMESTAMP,
T1_EVENTNAME,
T1_EVENTFIELD,
T1_EVENTVALUE
FROM #TEMP1
WHERE T1_EVENTNAME='LOGIN' AND T1_EVENTFIELD='SESSION_ID'

SELECT * FROM #TEMP2;
/*End Login and SESSION_Id*/

/*INSERTING JOINED TEMP TABLES INTO NEW TEMP TABLE */
DROP TABLE #TEMP12
CREATE TABLE #TEMP12
(T12_EVENTID CHAR(20),
T12_TIMESTAMP CHAR(50),
T12_EVENTNAME CHAR (50),

T12_EVENTVALUE CHAR(50),
T12_EVENTVALUE2 CHAR (50))

INSERT INTO #TEMP12



/* JOIN #TEMP2 AND #TEMP3*/

SELECT T2.T2_EVENTID,
T2.T2_TIMESTAMP,
T2.T2_EVENTNAME,

T2.T2_EVENTVALUE,
T3.T2_EVENTVALUE
FROM #TEMP2 T2
JOIN
#TEMP3 T3
ON
T2.T2_EVENTID=T3.T2_EVENTID

SELECT * FROM #TEMP12;

/*END JOIN OF TEMP1 AND TEMP2 STORED IN TEMP12*/

 

[JohnDTampaBay]

How is #TEMP3 created? I don't believe the code is provided. Sounds ike #TEMP3 contains multiple rows per T2_EVENTID. You may get around it with SELECT DISTINCT in the JOIN query, but T3.T2_EVENTVALUE may still cause multiple rows per T2_EVENTID. Good luck!


--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[SQLBill]

As John says...this section of the code is messed up:

/* JOIN #TEMP2 AND #TEMP3*/

SELECT T2.T2_EVENTID,
T2.T2_TIMESTAMP,
T2.T2_EVENTNAME,

T2.T2_EVENTVALUE,
T3.T2_EVENTVALUE
FROM #TEMP2 T2
JOIN
#TEMP3 T3
ON
T2.T2_EVENTID=T3.T2_EVENTID

SELECT * FROM #TEMP12;

/*END JOIN OF TEMP1 AND TEMP2 STORED IN TEMP12*/


You start off saying you are joining TEMP2 and TEMP3 and that's what you do in the code. But you close it by saying you ended joining temp1 and temp2. That never happened. Instead you joined temp2 to a non-existant table.

Run the separate parts of code that create the initial temp tables. Then SELECT * from those tables. See what it returns and if it's what you expect. Then correct your joined table.

-SQLBill

Posting advice: FAQ481-4875