SQL select compare - 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

General SQL Server Forums

New to SQL Server Programming

SQL select compare

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-10-14 : 04:12:32

Hi,

I have 2 table. T1 and T2 as below

T1

userid department
____________________
john NULL
peter NULL

T2

useremail Department
___________________________
john@email.com IT
peter@email.com ACCount
.....@email.com Sale

My question is how to i map T2 to T1 where the email is fix to userid + domain name.

UPDATE T1 SET department= (SELECT department FROM t2 WHERE department = t1.department)

my problem is how to subtract the useremail peter@email.com = to peter ??

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 04:24:39

Select left('peter@email.com',charindex('@','peter@email.com')-1)

Madhivanan

Failing to plan is Planning to fail

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-10-14 : 04:42:26

quote:
Originally posted by madhivanan

Select left('peter@email.com',charindex('@','peter@email.com')-1)

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan,

This query doesn't work.
but after i take out the -1 then it will retrieve peter@ but not peter.

why i can't put -1 there?

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-10-14 : 04:52:11

quote:
Originally posted by madhivanan

Select left('peter@email.com',charindex('@','peter@email.com')-1)

Madhivanan

Failing to plan is Planning to fail

One more funny thing happen, when i change -1 to +1 it work and what i get is peter@e.

Why we can't put -1? is there other way to do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 05:02:42

quote:
Originally posted by calvinkwoo3000

quote:
Originally posted by madhivanan

Select left('peter@email.com',charindex('@','peter@email.com')-1)

Madhivanan

Failing to plan is Planning to fail

One more funny thing happen, when i change -1 to +1 it work and what i get is peter@e.

Why we can't put -1? is there other way to do that?

no problem in putting -1. can you show query used? also what happened when u put -1? did it gave any error?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 05:10:46

try this out

UPDATE t1
SET t1.department=t2.department
FROM T1 t1
JOIN T2 t2
ON t1.userid=LEFT(t2.useremail,CASE WHEN CHARINDEX('@',t2.useremail)>0 THEN CHARINDEX('@',t2.useremail) ELSE LEN(t2.useremail)+1 END -1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 05:53:50

[code]DECLARE @t1 TABLE
(
userID VARCHAR(20),
department VARCHAR(20)
)

INSERT @t1
SELECT 'john', NULL UNION ALL
SELECT 'peter', NULL

DECLARE @t2 TABLE
(
userEmail VARCHAR(100),
Department VARCHAR(100)
)

INSERT @t2
SELECT 'john@email.com', 'IT' UNION ALL
SELECT 'peter@email.com', 'ACCount' UNION ALL
SELECT '.....@email.com', 'Sale'

SELECT *
FROM @t1

UPDATE x
SET x.department = g.department
FROM @t1 AS x
INNER JOIN @t2 AS g ON LEFT(g.useremail, charindex('@', g.useremail) - 1) = x.userid

SELECT *
FROM @t1[/code]

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

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-10-14 : 05:58:52

quote:

Originally posted by visakh16

try this out

UPDATE t1
SET t1.department=t2.department
FROM T1 t1
JOIN T2 t2
ON t1.userid=LEFT(t2.useremail,CASE WHEN CHARINDEX('@',t2.useremail)>0 THEN CHARINDEX('@',t2.useremail) ELSE LEN(t2.useremail)+1 END -1)

]

Thank you visakh16, it solve my problem.
Thank to madhivanan too.

the query that i used is Select left(useremail,charindex('@',useremail)-1) from t1
The error message when i put in the -1 = Invalid length parameter passed to the SUBSTRING function

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 06:03:51

It means you have emails without "@", which is a sign of storing bad data in the table.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 06:16:43

quote:
Originally posted by Peso

It means you have emails without "@", which is a sign of storing bad data in the table.

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

i guessed it right then

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-10-14 : 06:21:06

quote:
Originally posted by visakh16

quote:
Originally posted by Peso

It means you have emails without "@", which is a sign of storing bad data in the table.

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

i guessed it right then

hahhah, dont know, i guessed it right too, but not working. heheheh

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-10-15 : 02:29:27

quote:
Originally posted by visakh16

quote:
Originally posted by Peso

It means you have emails without "@", which is a sign of storing bad data in the table.

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

i guessed it right then

Hi all, my problem still there, below is my query.
UPDATE t1
SET department = tb2.department
FROM t1 AS tb1 INNER JOIN
t2 AS tb2 ON tb1.userid = LEFT(tb2.useremail, CASE WHEN CHARINDEX('@', tb2.useremail) > 0 THEN CHARINDEX('@', tb2.useremail)
ELSE LEN(tb2.useremail) + 1 END - 1) CROSS JOIN
t1

don't know why the mapping is wrong.
can any one told me why???

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-10-15 : 02:39:24

Can we restart my problem??

T1

userid department
_________________________
JEFF NULL
John NULL
Peter NULL

T2
Email Department
__________________________
Jeff@domain.com IT
John@domain.com Resign
Peter@domain.com Old

what i want is update set T1 to

T1

userid Department
_________________________
JEFF IT
JoHN Resign
peter old

but after i run my sql statement below

UPDATE t1
SET department = tb2.department
FROM t1 AS tb1 INNER JOIN
t2 AS tb2 ON tb1.userid = LEFT(tb2.email, CASE WHEN CHARINDEX('@', tb2.email) > 0 THEN CHARINDEX('@', tb2.email)
ELSE LEN(tb2.email) + 1 END - 1) CROSS JOIN
t1

my result as below :

JEFF IT
john OLD
peter IT

What wrong with the query or is there other sql to solve?
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-15 : 03:17:48

You dont need a cross join. Try

UPDATE t1
SET department = tb2.department
FROM t1 AS tb1 INNER JOIN
t2 AS tb2 ON tb1.userid = LEFT(tb2.email, CASE WHEN CHARINDEX('@', tb2.email) > 0 THEN CHARINDEX('@', tb2.email)
ELSE LEN(tb2.email) + 1 END - 1)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 03:45:16

calvinkwoo3000,what was the reason for adding CROSS JOIN?

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-10-15 : 04:31:28

quote:
Originally posted by visakh16

calvinkwoo3000,what was the reason for adding CROSS JOIN?

i didn't put the cross join, but when i excute in the Sql query, the cross join is added itself

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 04:33:10

quote:
Originally posted by calvinkwoo3000

quote:
Originally posted by visakh16

calvinkwoo3000,what was the reason for adding CROSS JOIN?

i didn't put the cross join, but when i excute in the Sql query, the cross join is added itself

which tool you're using?

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-10-15 : 04:36:10

SQl server management studio 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 04:37:42