Combine Data - 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)

Combine Data

Author

Topic

sfjtraps
Yak Posting Veteran

65 Posts

Posted - 2009-08-04 : 11:40:02

I have two tables that both have columns called Name and the names are like each other. However the name in one table has an ID of 10 and the name in the other column has an ID of 20. I want to associate both values to one of the column names in a separate table. So I want to take the two tables below and create table 3.

Table 1 ID
apples 10

Table 2 ID
aFruit 20

Table 3 ID ID2
apples 10 20

Can anyone help with this please?

Thank you,

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 11:44:37

Your table2 name is nothing like your table 1 name.

Is that what you really wanted?

Or is there some way to build a relationship between the tables we are not aware of?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sfjtraps
Yak Posting Veteran

65 Posts

Posted - 2009-08-04 : 12:06:37

Actually you're right, I wasn't specific enough. What I actually want is as follows:

Table1 ID
apples 10
oranges 20

Table2 ID
apples1 12
oranges1 22

Table3 ID ID2
apples 10 12
oranges 20 22

Sorry for the misinterpretation. Any help would be greatly appreciated.

thank you,

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-04 : 12:11:31

What if your data is like this?

Table1 ID
sands 10
tea 20

Table2 ID
sandskrit 12
sandstorm 22
tealeaves 10
teastain 25

In other words are the values in table1 just a substring of the values in table2 or do the values in table2 always have just a number at the end?

An infinite universe is the ultimate cartesian product.

sfjtraps
Yak Posting Veteran

65 Posts

Posted - 2009-08-04 : 12:22:58

The name in table2 two are exactly the same as table1, except the ones in table2 have a consistent text appended to the beginning exactly as follows:

Table1
apples
oranges

Table2
RootCause1apples
RootCause1oranges

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 12:24:56

is that "consistent text" a constant? or do you need to be able to join on a variable + <name from table1> condition?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 12:30:41

If yo can guarantee that there is only 1 match in the 2nd table and to only over want 2 [Id] columns returned then this will do it


DECLARE @matchString VARCHAR(255)      SET @matchString = 'foo1'

DECLARE @table1 TABLE (
	[name] VARCHAR(255)
	, [ID] INT
	)

DECLARE @table2 TABLE (
	[name] VARCHAR(255)
	, [Id] INT
	)

INSERT @table1 ([name], [Id])
      SELECT 'apples', 10
UNION SELECT 'oranges', 20

INSERT @table2 ([name], [ID])
      SELECT 'foo1apples', 23
UNION SELECT 'foo1oranges', 1
UNION SELECT 'abcapples', 10

SELECT * FROM @table1
SELECT * FROM @table2

SELECT
	t1.[name] AS [Name]
	, t1.[ID] AS [Id1]
	, t2.[ID] AS [Id2]
FROM
	@table1 t1
	JOIN @table2 t2 ON @matchString + t1.[name] = t2.[name]

However, I suspect that isn't what you want.....

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources