select query
-
hi
in sql 2000 ,i have 3 fields in a table called
COMPANY,CONTACT and VALUE
with various values eg.
COMPANY CONTACT VALUE
TAN *CMPY* NULL
TAN smith NULL
TAN bloggs BYG
RED *CMPY* XYZ
RED MORGAN NULL
RED JONES NULL
SO BASICALLY WHEN *CMPY* WITHIN COMPANY TAN HAS VALUE NULL I WANT ALL OTHER CONTACTS WITHIN THAT COMPANY TO HAVE THE SAME VALUE AS *CMPY* AND SHOW ME THE CONTACTS THAT DONT
I WANT A QUERY THAT WILL RETURN ALL OTHER CONTACTS PER COMPANY THAT DOES NOT HAVE THE SAME VALUE OF CONTACT *CMPY*
IM STUMPED ANY HELP WOULD BE REALLY APPRECIATED
THANKING YOU KINDLY
-
Here's a sample:
CREATE table COMpanies (COMPANY varchar(20),CONTACT varchar(15), VALUE varchar(10))
Insert into Companies (COMPANY, CONTACT, VALUE)
Values ('TAN', '*CMPY*', NULL)
Insert into Companies (COMPANY, CONTACT, VALUE)
Values ('TAN', 'Smith', NULL)
Insert into Companies (COMPANY, CONTACT, VALUE)
Values ('TAN', 'Blogs', 'XYZ')
Insert into Companies (COMPANY, CONTACT, VALUE)
Values ('RED', '*CMPY*', NULL)
Insert into Companies (COMPANY, CONTACT, VALUE)
Values ('RED', 'Morgan', NULL)
Insert into Companies (COMPANY, CONTACT, VALUE)
Values ('RED', 'Jones', 'XYZ')
Insert into Companies (COMPANY, CONTACT, VALUE)
Values ('RED', 'Jonses', 'AXYZ')
Insert into Companies (COMPANY, CONTACT, VALUE)
Values ('BLUE', '*CMPY*', 'pp')
Insert into Companies (COMPANY, CONTACT, VALUE)
Values ('BLUE', 'Billy', NULL)
Select * from Companies
Select c2.* from Companies c1
Join Companies c2 ON c1.COMPANY = C2.COMPANY
WHERE c1.Contact = '*CMPY*'
AND ISNULL(C1.Value,'') <> ISNULL(c2.Value,'')
-- if you want to do this only when Value is null
Select c2.* from Companies c1
Join Companies c2 ON c1.COMPANY = C2.COMPANY
WHERE c1.Contact = '*CMPY*' AND c1.VALUE IS NULL
AND ISNULL(C1.Value,'') <> ISNULL(c2.Value,'')
Does this help?
Francis
-
THATS GREAT THANK YOU VERY MUCH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply