Hi All,
I have table with 2 cols 1)ObjectName 2)Owner (omlitted other cols for
simplicity)
ObjectName Owner
obj1 own1
obj1 own2
obj2 own1
obj2 own2
obj3 own1
obj4 own2
if ObjectName is present for own1 It should also be there for own2 &
other way around if ObjectName is present for own2 it should also be
there for own1. I want to find out rows which does not meet such
criteria. like in above example last 2 rows for obj3 & obj4.
As obj3 is present for only own1 & obj4 is present for own2 only.
Could any one guide me how to go about it?
Thanks in advance for help
VinodVinod
CREATE TABLE dbo.Stuffs (
obj_name VARCHAR(4) NOT NULL
,owner VARCHAR(4) NOT NULL
CHECK (owner IN ('own1', 'own2'))
,PRIMARY KEY (obj_name, owner)
);
INSERT INTO Stuffs VALUES('obj1', 'own1');
INSERT INTO Stuffs VALUES('obj1', 'own2');
INSERT INTO Stuffs VALUES('obj2', 'own1');
INSERT INTO Stuffs VALUES('obj2', 'own2');
INSERT INTO Stuffs VALUES('obj3', 'own1');
INSERT INTO Stuffs VALUES('obj4', 'own2');
SELECT obj_name FROM Stuffs
GROUP BY obj_name
HAVING COUNT(owner) <> 2
;
Joe|||create table foo (objectname varchar(5), owner varchar(5))
insert into foo values ('obj1','own1')
insert into foo values ('obj1','own2')
insert into foo values ('obj2','own1')
insert into foo values ('obj2','own2')
insert into foo values ('obj3','own1')
insert into foo values ('obj4','own2')
insert into foo values ('obj4','own3')
insert into foo values ('obj4','own3')
select every.*
from
(select obj.objectname, own.owner from (select distinct objectname from foo)
obj
cross join (select distinct owner from foo) own ) every
where objectname+owner not in (select objectname+owner from foo)
"vinod" <vinod.patil1@.gmail.com> wrote in message
news:1132180115.463350.278900@.g44g2000cwa.googlegroups.com...
> Hi All,
> I have table with 2 cols 1)ObjectName 2)Owner (omlitted other cols for
> simplicity)
> ObjectName Owner
> obj1 own1
> obj1 own2
> obj2 own1
> obj2 own2
> obj3 own1
> obj4 own2
> if ObjectName is present for own1 It should also be there for own2 &
> other way around if ObjectName is present for own2 it should also be
> there for own1. I want to find out rows which does not meet such
> criteria. like in above example last 2 rows for obj3 & obj4.
> As obj3 is present for only own1 & obj4 is present for own2 only.
> Could any one guide me how to go about it?
> Thanks in advance for help
> Vinod
>|||Why do you think that EVERY is a keyword in SQL?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132196174.489954.44330@.g14g2000cwa.googlegroups.com...
> Why do you think that EVERY is a keyword in SQL?
>
He doesn't think that. He thinks it's a table alias, because that
is what it is being used for.
Sincerely,
Chris O.
No comments:
Post a Comment