An interesting Qn

Hi,
My table has the following structure
create table CompanyRights (
CompanyID int, RightID int )
Insert into CompanyRights select 1,1
Insert into CompanyRights select 1,2
Insert into CompanyRights select 1,3
Insert into CompanyRights select 2,1
Insert into CompanyRights select 2,1
and i want to choose all the companies having rights 1 and 2
select * from CompanyRights where CompanyID = 1 And RightID = 1 AND RightID
= 2
but this didn't work
Regards
LaraIs it possible without a join
With Join I have the answer
SELECT t1.CompanyID,T1.RightID
FROM CompanyRights t1
INNER join CompanyRights t2
ON t1.CompanyID =t2.CompanyID
WHERE t1.RightID = 1 AND t2.RightID = 2
regards Lara|||select * from CompanyRights where CompanyID = 1 And RightID = 1 AND RightID
= 2
The key RightID cant be 1 AND 2 at the same time, this must be
select * from CompanyRights where CompanyID = 1 And (RightID = 1 OR RightID
= 2)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lara" <aneeshattingal@.hotpop.com> schrieb im Newsbeitrag
news:O$6pQLaSFHA.248@.TK2MSFTNGP15.phx.gbl...
> Is it possible without a join
> With Join I have the answer
> SELECT t1.CompanyID,T1.RightID
> FROM CompanyRights t1
> INNER join CompanyRights t2
> ON t1.CompanyID =t2.CompanyID
> WHERE t1.RightID = 1 AND t2.RightID = 2
> regards Lara
>
>|||Sure,
Select * From CompanyRights
Where RightID In (1,2)
-- which is same as
Select * From CompanyRights
Where RightID = 1 Or RightID = 2
"Lara" wrote:

> Is it possible without a join
> With Join I have the answer
> SELECT t1.CompanyID,T1.RightID
> FROM CompanyRights t1
> INNER join CompanyRights t2
> ON t1.CompanyID =t2.CompanyID
> WHERE t1.RightID = 1 AND t2.RightID = 2
> regards Lara
>
>|||Try,
SELECT
CompanyID
FROM
CompanyRights
WHERE
RightID = 1
or RightID = 2
group by
CompanyID
having
count(distinct RightID) = 2;
Relational Division
http://www.dbazine.com/ofinterest/o...br />
division
AMB
"Lara" wrote:

> Is it possible without a join
> With Join I have the answer
> SELECT t1.CompanyID,T1.RightID
> FROM CompanyRights t1
> INNER join CompanyRights t2
> ON t1.CompanyID =t2.CompanyID
> WHERE t1.RightID = 1 AND t2.RightID = 2
> regards Lara
>
>|||Select Distinct CompanyID
From CompanyRights R
Where Exists
(Select * From CompanyRights
Where CompanyID = R.CompanyID
And RightID = 1)
And Exists
(Select * From CompanyRights
Where CompanyID = R.CompanyID
And RightID = 2)
"Lara" wrote:

> Hi,
> My table has the following structure
> create table CompanyRights (
> CompanyID int, RightID int )
> Insert into CompanyRights select 1,1
> Insert into CompanyRights select 1,2
> Insert into CompanyRights select 1,3
> Insert into CompanyRights select 2,1
> Insert into CompanyRights select 2,1
> and i want to choose all the companies having rights 1 and 2
> select * from CompanyRights where CompanyID = 1 And RightID = 1 AND Right
ID
> = 2
> but this didn't work
> Regards
> Lara
>
>