Joining two tables with different data setup...

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 2000 Forums

Transact-SQL (2000)

Joining two tables with different data setup...

Author

Topic

Lopaka
Starting Member

48 Posts

Posted - 2006-05-18 : 12:31:21

I am trying to get the data out of temp2 but the data need to join the two tables are different. Is there a way to do this??? The following is a small set of the data. The actual temp table is over 500K records and temp2 is over a 1Bil records.

IF OBJECT_ID('tempdb.dbo.#TEMP') IS NOT NULL DROP TABLE #TEMP
GO
IF OBJECT_ID('tempdb.dbo.#TEMP2') IS NOT NULL DROP TABLE #TEMP2
GO

CREATE TABLE #TEMP(HEX CHAR(8), TYPE CHAR(2), N CHAR(9), LIST NTEXT)
CREATE TABLE #TEMP2(HEX CHAR(8))

INSERT INTO #TEMP(HEX, TYPE, N, LIST) VALUES('0043CB85','EO','383097538','0043CB85')
INSERT INTO #TEMP(HEX, TYPE, N, LIST) VALUES('01200137','EO','411662715','01200137;01200138;01200139')
INSERT INTO #TEMP(HEX, TYPE, N, LIST) VALUES('01200F10','EO','731664534','01200f10;01200f11;01200f12;01200f13;01200f14;01200f15')

INSERT INTO #TEMP2(HEX) VALUES('0043CB85')
INSERT INTO #TEMP2(HEX) VALUES('01200137')
INSERT INTO #TEMP2(HEX) VALUES('01200138')
INSERT INTO #TEMP2(HEX) VALUES('01200139')
INSERT INTO #TEMP2(HEX) VALUES('01200f10')
INSERT INTO #TEMP2(HEX) VALUES('01200f11')
INSERT INTO #TEMP2(HEX) VALUES('01200f12')
INSERT INTO #TEMP2(HEX) VALUES('01200f13')
INSERT INTO #TEMP2(HEX) VALUES('01200f14')
INSERT INTO #TEMP2(HEX) VALUES('01200f15')

Robert R. Barnes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-18 : 12:39:23

What are you trying to do?

Extract all rows that are equal?
All rows that are in temp2 but not temp?
All rows that are in temp but not temp2?

Lopaka
Starting Member

48 Posts

Posted - 2006-05-18 : 12:58:39

I am trying to perform the following join:
select *
from #temp2 a LEFT JOIN
#temp b ON b.list like a.hex

except it does not work???
Only returns one row...:(

Robert R. Barnes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-18 : 13:08:56

For the supplied dataset, which records would you like to fetch?

I get three rows just by shifting

select * from #temp2 a LEFT JOIN #temp b ON b.list like a.hex

to

select * from #temp2 a LEFT JOIN #temp b ON a.hex like b.list

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-18 : 13:21:53

Here is a query where you get all the matching rows between #Temp and #Temp2

SELECT #Temp.*
FROM #Temp2
INNER JOIN #Temp ON #Temp2.HEX = #Temp.HEX

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-19 : 02:37:42

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources