September 27, 2011 at 7:04 am
There is a table in my database that stores client information. There is a second table which stores details for people who might be contacts for that client. The clients information is automatically stored in the contacts table.
Example 1
client table = JB1 (Joe Bloggs)
contacts table = JB1 (Joe Bloggs)
Example 2
client table = JB1 (Joe Bloggs)
contacts table = JB1 (Joe Bloggs)
JB2 (Joanne Bloggs)
I want to produce a list of clients that have other contacts linked to them (e.g. contacts other than themselves as in example 2).
I've got a query that passes the ID code for each person to a variable. I want to compare the data in both variables, and then only return the ID codes for people where the ID codes are different, but I don't know what the correct function is to make the comparison
declare @clientcode varchar(15)
declare @contactcode varchar(15)
set @clientcode = (select entityref from std_entitycontacts)
set @contactcode = (select contactref from std_entitycontacts)
if (@clientcode not like @contactcode)
select entityref, contactref
from std_entitycontacts
I'm new at this, so apologies if the answer is blindingly obvious!
Thanks
September 28, 2011 at 9:39 am
sjerromeharris (9/27/2011)
There is a table in my database that stores client information. There is a second table which stores details for people who might be contacts for that client. The clients information is automatically stored in the contacts table.Example 1
client table = JB1 (Joe Bloggs)
contacts table = JB1 (Joe Bloggs)
Example 2
client table = JB1 (Joe Bloggs)
contacts table = JB1 (Joe Bloggs)
JB2 (Joanne Bloggs)
I want to produce a list of clients that have other contacts linked to them (e.g. contacts other than themselves as in example 2).
I've got a query that passes the ID code for each person to a variable. I want to compare the data in both variables, and then only return the ID codes for people where the ID codes are different, but I don't know what the correct function is to make the comparison
declare @clientcode varchar(15)
declare @contactcode varchar(15)
set @clientcode = (select entityref from std_entitycontacts)
set @contactcode = (select contactref from std_entitycontacts)
if (@clientcode not like @contactcode)
select entityref, contactref
from std_entitycontacts
I'm new at this, so apologies if the answer is blindingly obvious!
Thanks
First you would want to use the <> (not equal to) operator when comparing varchar strings. Second when populating a variable you would want to do this:
select @clientcode = entityref from std_entitycontacts
The issue is you don't have any where clause, so you would want to return a specific record.
Third you might want to consider a join, it is hard to what the exact join would look like, but here is my guess:
select se.entityref, se2.contactref from std_entitycontacts se
join std_entitycontacts se2 on se.somesortofIDhere = se2.somesortofIDhere
where se.entitref <> se2.contactref
You would need to replace the somesortofIDhere with the actual key that links the two tables together. Hope that helps.
Ben
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply