March 30, 2009 at 1:33 pm
Hi all
I've got this scenari
table1
id Name
10 John
11 Alice
12 Peter
table2
id Name
4 John
5 Alice
6 Peter
table3
id surname
4 Smith
5 Fratello
6 Jordan
I wonder if is possible go to this table
table3
id Surname
10 Smith
11 Fratello
12 Jordan
table3 is a new table and I want to normalize with the rest one, table2 would be deleted.
Thank you
March 30, 2009 at 1:54 pm
Hi,
Are you wanting to join table1 and table2 based on firstname and then join table2 and table3 based on ID? Try the code below and if that does not work you may need to be a bit more specific with what you are after.
create table #table1(id int, firstname varchar(32))
insert #table1
select 10, 'John'
union all select 11, 'Alice'
union all select 12, 'Peter'
create table #table2(id int, firstname varchar(32))
insert #table2
select 4 ,'John'
union all select 5 ,'Alice'
union all select 6 ,'Peter'
create table #table3(id int, surname varchar(32))
insert #table3
select 4 ,'Smith'
union all select 5 ,'Fratello'
union all select 6 ,'Jordan'
-- View the three tables joined together
select *
from #table1
inner join #table2 on #table1.firstname = #table2.firstname
inner join #table3 on #table2.id = #table3.id
-- Now you can make your new table
select #table1.id , #table3.surname
into #newTable
from #table1
inner join #table2 on #table1.firstname = #table2.firstname
inner join #table3 on #table2.id = #table3.id
Bevan
March 30, 2009 at 1:56 pm
Hi
First you have to join the id in table t3 with the id in t2. Now you have to join the name of t2 with the name of t1 and you got all values.
DECLARE @t1 TABLE (id INT, name VARCHAR(100))
DECLARE @t2 TABLE (id INT, name VARCHAR(100))
DECLARE @t3 TABLE (id INT, name VARCHAR(100))
INSERT INTO @t1
SELECT 10, 'John'
UNION SELECT 11, 'Alice'
UNION SELECT 12, 'Peter'
INSERT INTO @t2
SELECT 4, 'John'
UNION SELECT 5, 'Alice'
UNION SELECT 6, 'Peter'
INSERT INTO @t3
SELECT 4, 'Smith'
UNION SELECT 5, 'Fratello'
UNION SELECT 6, 'Jordan'
UPDATE t3 SET t3.id = t1.id
FROM @t3 t3
JOIN @t2 t2 ON t3.id = t2.id
JOIN @t1 t1 ON t2.name = t1.name
SELECT * FROM @t3
Greets
Flo
March 30, 2009 at 3:47 pm
I have to ask why you are using different tables.
You can certainly join any of those tables that have common ID to get a first name/last name pair, but why are you not joining those columns together in a single table?
Is this to create test data or something?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 31, 2009 at 1:33 am
First of all, thank you for your time.
I know that this is a strange question, but It's a job that falling me suddelny.
There is an ugly database in mysql that i have to normalize in SQL Server, due to I have to create and delete tables.
I'm going to try your response.
March 31, 2009 at 3:34 am
It works perfectly, thank you very much. 😀
March 31, 2009 at 7:23 am
Thank you for taking the time to explain.
Good job, Flo 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 31, 2009 at 12:48 pm
Thanks Bob! 🙂
March 31, 2009 at 1:06 pm
No problem, Flo.
Florian is a new name to me. May I ask if you are a man or a woman? I try to keep things straight since I once referred to Lynn Pettis as "her".
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 31, 2009 at 1:09 pm
Hi Bob
I'm a man. Florian is a German name. Maybe you know Munich (Bavaria). The city with the "Oktoberfest" 😉
Greets
Flo
March 31, 2009 at 1:43 pm
Thanks Flo, I was looking for similar kind of query and your work does that.
March 31, 2009 at 1:50 pm
You are welcome!
March 31, 2009 at 2:20 pm
I've never had the pleasure of visiting Germany, Flo. Perhaps one day. It's nice to "meet" you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 31, 2009 at 2:33 pm
Just let me know when you plan your trip.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply