Joining different data types

I have a table, I'll call contacts, where the primary key is called id and
it's an integer field. I have another table, I'll call tempcontacts, and
it's primary key is id but it's a guid for many reasons I won't go into now.
These 2 tables are never combined in the same query.
Table 3 is a ClientDetails table, with the ClientID a varchar to accommodate
either a guid or an integer. This works okay until I try to do a query with
joins, I can't join either table's id with the ClientDetails
ClientID/varchar field. Can I use convert or cast in my query? Or how can
I weed out, for instance, only the numeric ClientID's, then join it.
Thanks for your help, I'm really not sure what to do about this.You can do both, but converting the joined columns will lead into
performace issues.
HTH, jens Suessmeyer.|||Uniqueidentifier has a higher data-type precedence that varchar, so when
comparing the varchar field to the uniqueidentifier field, SQL tries to
implicitly convert the varchar field to uniqueidentifier. You get the error
when it tries to convert one of the ClientID values that represents an
integer into uniqueidentifier.
Try
SELECT
....
FROM Contacts
INNER JOIN ClientDetails ON Contacts.id =
CASE WHEN ISNUMERIC(ClientDetails.ClientID) = 1 THEN ClientDetails.ClientID
ELSE -10 END
--The key thing here is that the CASE expression returns the value of
ClientDetails.ClientID to -10 whenever ClientID is not numeric (in other
words, whenver it is a uniqueidentifier). I chose -10 because I'm guessing
there are no negative values of ID in your contacts table. If there are,
adjust accordingly.
For the other table.
SELECT
...
FROM tempcontacts
INNER JOIN ClientDetails ON tempcontacts.id =
CASE WHEN ISNUMERIC(ClientDetails.ClientID) = 0 THEN ClientDetails.ClientID
ELSE NEWID() END
Again, the logic here is that for any integer values of
ClientDetails.ClientID, the value for the uniqueidentifier will be set to a
value returned by NEWID(), which (hopefully) will not match anything in
tempcontacts.id.
dew" wrote:

> I have a table, I'll call contacts, where the primary key is called id and
> it's an integer field. I have another table, I'll call tempcontacts, and
> it's primary key is id but it's a guid for many reasons I won't go into no
w.
> These 2 tables are never combined in the same query.
> Table 3 is a ClientDetails table, with the ClientID a varchar to accommoda
te
> either a guid or an integer. This works okay until I try to do a query wi
th
> joins, I can't join either table's id with the ClientDetails
> ClientID/varchar field. Can I use convert or cast in my query? Or how ca
n
> I weed out, for instance, only the numeric ClientID's, then join it.
> Thanks for your help, I'm really not sure what to do about this.
>
>