why won't this join work? - 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 2000 Forums

Transact-SQL (2000)

why won't this join work?

Author

Topic

Zim327
Yak Posting Veteran

62 Posts

Posted - 2006-12-20 : 16:59:13

Hi all,
I'm simply trying to find who has taken a course (there's 30 people). all I have is their first and last name to go on, unfortunately.
I thought it would be simple but this query has no results!
select b.user_id, b.Last_name, b.first_name, t.last_name, t.first_name
from users b, temp_stuff t where b.last_name = t.last_name and b.first_name = t.first_name order by b.last_name

what am i doing wrong?

Thanks,

Best regards,
Zim
(Future Eternal Yak God Emperor)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-20 : 17:25:04

Your query looks correct, so perhaps there aren't any matching rows between your two tables.

BTW, you should be using the ANSI JOIN syntax:

select b.user_id, b.Last_name, b.first_name, t.last_name, t.first_name
from users b
inner join temp_stuff t
on b.last_name = t.last_name and b.first_name = t.first_name
order by b.last_name

Tara Kizer

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-20 : 22:33:38

While comparing character data, I always keeps the issue of trailing spaces in my mind. Check this:

select b.user_id, b.Last_name, b.first_name, t.last_name, t.first_name 
from users b
inner join temp_stuff t 
on ltrim(rtrim(b.last_name)) = ltrim(rtrim(t.last_name)) and ltrim(rtrim(b.first_name)) = ltrim(rtrim(t.first_name))
order by b.last_name

Although it's bit inefficient, can't help !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 00:31:00

You mean "can't do any harm" I hope?
Because "if it can't help" then posting the query is... BAD

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-21 : 03:05:25

quote:
Originally posted by Peso

You mean "can't do any harm" I hope?
Because "if it can't help" then posting the query is... BAD

Peter Larsson
Helsingborg, Sweden

Can't help regarding the inefficiency of the query, not the working of the query!

Peter, You are seems to be in naughty mood!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 03:29:32

No worry for trailing spaces!

declare @t1 table (data varchar(10))

insert	@t1
select	'peso' union all
select	'harsh'

declare @t2 table (data char(10))

insert	@t2
select	'peso  ' union all
select	'harsh   '

select		'_' + t1.data + '_' t1_data,
		'_' + t2.data + '_' t2_data
from		@t1 t1
inner join	@t2 t2 on t2.data = t1.data

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-21 : 04:55:59

Yes, but what if he/she has leading spaces?

Because OP has never said any word about how his data is stored and what data type he/she is using? and since he/she is not getting any records in the join, there seems to be possibility of leading/trailing spaces.

Also, for that matter, I can also consider his/her records containing non-printing characters!!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Zim327
Yak Posting Veteran

62 Posts

Posted - 2006-12-21 : 10:01:33

Harsh you are the man! It was the whitespace! (or the salmon pate! *monty python*)
I knew there plenty of results in there but the whitespace was giving me zero

thanks to one and all!

Best regards,
Zim
(Future Eternal Yak God Emperor)

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-21 : 10:05:08

quote:
Originally posted by Zim327
(Future Eternal Yak God Emperor)

Well, we'll have to see what the goddess has to say about that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 10:10:20

First time I have heard of LEADING white space is missed in initial investigation...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources