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.
>
>
Showing posts with label contacts. Show all posts
Showing posts with label contacts. Show all posts
Monday, March 12, 2012
Monday, February 20, 2012
Join one to many relationship query
I have 2 tables 'contacts' and 'history' I want to search 3 fields the body fields in the contacts table... and also the subject and body field in the history table all looking for like '%Winn%' and where contacts.address is not null.. I am having a heck of a time trying to do this... can anyone help please I want to return address and contactID.
GhornetI think that this should work... I am a little new at this though...
Select contact.contactId, contact.address from contacts
inner join contacts
where (history.body = contacts.body and contacts.address is NOT NULL) and history.body like '%Winn%'
Good luck.|||Sorry I meant:
inner join history|||Won't that join where the bodys are the same.. I don't want that...
Thanks
I have 2 tables 'contacts' and 'history'
I want to search 3 fields the body fields in the contacts table with like '%Winn%'
and also the subject and body field in the history table also looking for like '%Winn%'
I want to make sure that contacts.address and contacts.city is not null..
I want to return firstname, lastname, spouse, salutation, address, city, state, zip and contactID.
Ghornet|||something like this maybe:
select c.contactid, c.address from contacts c
where exists (select 1 from ((select contactid from history where subject like '%Winn%') union (select contactid from history where body like '%Winn%')) x where c.contactid = x.contactid)|||oh, and of course:
...and c.address is not null|||hmmm, I think I missed the body field in the contacts table. Anybody?|||How do they join? By contactID? If so, also, you mention the columns (there are no fields in relational theory, but we all know what you mean) you want to return but not from which table so I assume from the contacts table.
select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city, a.state, a.zip a.contactID
from contacts a
join history b on a.contactid = b.contactid
where b.subject not '%Winn%' and
a.body like '%Winn%' and
b.body like '%Winn%' and
a.address is not null
and a.city is not null
Did I miss anything?|||i think that is what I want thanks a bunch for the help
select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city, a.state, a.zip a.contactID
from contacts a
join history b on a.contactid = b.contactid
where b.subject like '%Winn%' or
a.body like '%Winn%' or
b.body like '%Winn%' and
a.address is not null
and a.city is not null|||I think it is not.
Better put some parenthesis around that complicated WHERE clause just to be sure:
select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city,
a.state, a.zip, a.contactID
from contacts a
inner join history b on a.contactid = b.contactid
where (b.subject like '%Winn%' or a.body like '%Winn%' or b.body like '%Winn%')
and a.address is not null
and a.city is not null
blindman
GhornetI think that this should work... I am a little new at this though...
Select contact.contactId, contact.address from contacts
inner join contacts
where (history.body = contacts.body and contacts.address is NOT NULL) and history.body like '%Winn%'
Good luck.|||Sorry I meant:
inner join history|||Won't that join where the bodys are the same.. I don't want that...
Thanks
I have 2 tables 'contacts' and 'history'
I want to search 3 fields the body fields in the contacts table with like '%Winn%'
and also the subject and body field in the history table also looking for like '%Winn%'
I want to make sure that contacts.address and contacts.city is not null..
I want to return firstname, lastname, spouse, salutation, address, city, state, zip and contactID.
Ghornet|||something like this maybe:
select c.contactid, c.address from contacts c
where exists (select 1 from ((select contactid from history where subject like '%Winn%') union (select contactid from history where body like '%Winn%')) x where c.contactid = x.contactid)|||oh, and of course:
...and c.address is not null|||hmmm, I think I missed the body field in the contacts table. Anybody?|||How do they join? By contactID? If so, also, you mention the columns (there are no fields in relational theory, but we all know what you mean) you want to return but not from which table so I assume from the contacts table.
select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city, a.state, a.zip a.contactID
from contacts a
join history b on a.contactid = b.contactid
where b.subject not '%Winn%' and
a.body like '%Winn%' and
b.body like '%Winn%' and
a.address is not null
and a.city is not null
Did I miss anything?|||i think that is what I want thanks a bunch for the help
select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city, a.state, a.zip a.contactID
from contacts a
join history b on a.contactid = b.contactid
where b.subject like '%Winn%' or
a.body like '%Winn%' or
b.body like '%Winn%' and
a.address is not null
and a.city is not null|||I think it is not.
Better put some parenthesis around that complicated WHERE clause just to be sure:
select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city,
a.state, a.zip, a.contactID
from contacts a
inner join history b on a.contactid = b.contactid
where (b.subject like '%Winn%' or a.body like '%Winn%' or b.body like '%Winn%')
and a.address is not null
and a.city is not null
blindman
Subscribe to:
Posts (Atom)