JOIN that will only "sometimes" be able to join records is failing

Question

Post reply

JOIN that will only "sometimes" be able to join records is failing

metalray

SSCommitted

Points: 1647
More actions
July 16, 2009 at 7:24 am

#207080

Hi Guys,
I have the following little join:
SELECT Users.Email
FROM EventParticipants RIGHT JOIN
Users ON
EventParticipants.ParticipantID = Users.UserId
My problem is that not every participantsID can be
joined with users.userid because not all have a right id.
The join does not work because it struggles with that fact:
Msg 8169, Level 16, State 2, Line 1
Conversion failed when converting from a character string to uniqueidentifier.
How can I build a "soft" try-to join join ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

NicDX SSC-Addicted Points: 414 More actions · Answer 1

Hi

Can you rather tell us what tables\table structures you have and what outcome you would like.

Im not sure I understand the question or the problem...

Thanks

metalray SSCommitted Points: 1647 More actions · Answer 2

Users

-------

Email (VARCHAR)

UserID (UNIQUEIDENTIFIER)

Participants

----------------

ParticipantID (VARCHAR)

///////DATA///////

Users

-------

bob@bob.com BA23213

mike@mike.com 3243A

Participants

----------------

3243A

As you can see, user bob@bob.com can not be joined because

the id does not match with on ein table participants.

So the result would just be

mike@mike.com

Alan. T. Hall of Fame Points: 3221 More actions · Answer 3

If these are both uniqueidentifiers you have to convert them to (var)char prior to matching them.

cast(1.id as char(36)) = cast(1.id as char(36))

metalray SSCommitted Points: 1647 More actions · Answer 4

thats right, users.userid is of datatype UNIQUEIDENTIFIER,

when I use a cast I get

cast(Users.UserId.id as varchar(100))

Cannot call methods on uniqueidentifier.

Alan. T. Hall of Fame Points: 3221 More actions · Answer 5

Unless you are doing a one time load where you can use SSIS fuzzy matching. You are going to need a rule such as split at the first space and then compare that. If a rule like that is possible you can load the matching primary keys into a temp table and then selecting the matched results.

Alan

Alan. T. Hall of Fame Points: 3221 More actions · Answer 6

How can you be joining a uniqueidentifier to any other data type? They will never match.

SELECT Users.Email

FROM Users usr

LEFT OUTER JOIN EventParticipants prt

ON CAST(prt.ParticipantID as char(36)) = CAST(usr.UserId as char(36))

Should work

metalray SSCommitted Points: 1647 More actions · Answer 7

oh gosh thats sounds complicated. I can do a loop in my client application that does the matching instead

onces the uniqueidentifier is then extracted and handled as varchar.

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 8

Alan (7/16/2009)
If these are both uniqueidentifiers you have to convert them to (var)char prior to matching them.
cast(1.id as char(36)) = cast(1.id as char(36))

Why would you think a conversion is needed?

DECLARE @Foo TABLE (Val UNIQUEIDENTIFIER)

INSERT @Foo

SELECT '39527EB5-A05E-4C9E-847A-EE4A83606A48'

UNION ALL SELECT '7BF11438-7B9F-481A-B257-A58C1E45CE2D'

DECLARE @bar TABLE (Val UNIQUEIDENTIFIER)

INSERT @bar

SELECT '39527EB5-A05E-4C9E-847A-EE4A83606A48'

UNION ALL SELECT '0C58D634-A2C0-4EEF-8F54-F296C971A31E'

SELECT *

FROM @Foo AS F

INNER JOIN @bar AS B

ON F.Val = B.Val EDIT: My Bad, I thought both columns where GUIDS.. But still no need to convert both of them, just one or the other.

metalray SSCommitted Points: 1647 More actions · Answer 9

metalray

SSCommitted

Points: 1647

July 17, 2009 at 2:26 am

#1026055

ok thanks guys! that helped!

Alan. T. Hall of Fame Points: 3221 More actions · Answer 10

You are right about the 2 guids of course that is a habit to assume one of them came from our mainframe where they are stored as strings so we almost always need one or more conversions.

Thanks,

Alan