IN statement query 
Author Message
 IN statement query

Sasha,

I have to admit to some confusion. So I'm guessing that the following is
what you want. Sorry if my confusion causes me to post a query that doesn't
meet your needs.

select *
from <table>
where exists (select * from t where PropertyId = 1)
and   exists (select * from t where PropertyId = 2)
and   exists (select * from t where PropertyId = 3)
and   exists (select * from t where PropertyId = 4)
and   exists (select * from t where PropertyId = 5)


Quote:
> select fields from table where table.propertyid in (1,2,3,4,5)

> This statement works fine if i only have one propertyid what if I have
> multiple and only want to return the fields which match all the cases in
my
> in statement?

> I can't use where propertyid = 1 and propertyid = 2 and propertyid = 3 and
> propertyid = 4 and propertyid = 5 because this will always bring back no
> result.

> An in works like an 'or' statement, I need an in that works like an 'and'.

> Is this possible?

> Thanks,
> Sasha



Wed, 24 Apr 2002 03:00:00 GMT
 IN statement query

If you are willing to put your feature list into a temporary table, the
following query seems to do the trick:

-- Your data
create table p (propertyid int, featureid int)
insert into p values (1, 1)
insert into p values (1, 3)
insert into p values (2, 1)
insert into p values (2, 2)
insert into p values (3, 3)

-- Your "parameter" list
create table #p (featureid int)
truncate table #p
insert into #p values (1)
insert into #p values (3)

-- This will only return the properties that contain all of the features in
the parameter list
select propertyid from p join #p on (p2.featureid = #p.featureid)
group by propertyid
having count(*) = (select count(*) from #p)

This solution assumes that each featureid is mentioned once for each
propertyid in the p table.  It also doesn't work if #p is empty (but it is
too early for me to come up with an outer join :-)

Lewis


Quote:
> Sorry to cause confusion, it's tricky to explain,

> Your solution almost had it.

> This is my statement after reading your post

>  select Distinct(tp.propertyid )
>   from tblproperty tp
>    where exists (select * from  tblproperty2feature where featureid =  1)
>   AND exists (select * from  tblproperty2feature where featureid =  4)
>   AND exists (select * from  tblproperty2feature where featureid =  5)

> This stll brought back every propertyid because every property had atleast
> one of these features and
> the exists statements always would execute true.

> I only want to return the properties that contain all of these features.

> Thanks for your help anyway, much appreciated,

> Sasha



> > Sasha,

> > I have to admit to some confusion. So I'm guessing that the following is
> > what you want. Sorry if my confusion causes me to post a query that
> doesn't
> > meet your needs.

> > select *
> > from <table>
> > where exists (select * from t where PropertyId = 1)
> > and   exists (select * from t where PropertyId = 2)
> > and   exists (select * from t where PropertyId = 3)
> > and   exists (select * from t where PropertyId = 4)
> > and   exists (select * from t where PropertyId = 5)



> > > select fields from table where table.propertyid in (1,2,3,4,5)

> > > This statement works fine if i only have one propertyid what if I have
> > > multiple and only want to return the fields which match all the cases
in
> > my
> > > in statement?

> > > I can't use where propertyid = 1 and propertyid = 2 and propertyid = 3
> and
> > > propertyid = 4 and propertyid = 5 because this will always bring back
no
> > > result.

> > > An in works like an 'or' statement, I need an in that works like an
> 'and'.

> > > Is this possible?

> > > Thanks,
> > > Sasha



Wed, 24 Apr 2002 03:00:00 GMT
 IN statement query
Sasha,

 I also am a little confused, but if I understand the question correctly,

If tblproperty2feature is such that for any PropertyID, a specified
FeatureID can only exists once,   (i.e., PropertyID and FeatureID form a
Unique Key or Potential Primary Key), then

Select PropertyID
From tblproperty2feature TPF
Group By PropertyID
Having Sum(Case When FeatureID In (1,4,5)
        Then 1 Else 0 End ) =  3
 -- If there were four numbers in the In, then the 3 would be a 4.

If on the other hand, The same combination of PropertyID and FeatureID can
occur Multiple Times, the first solution I can think of is to Make a virtual
table that only has unique combinations....

Select Distinct PropertyID, FeatureID
From tblproperty2feature

And run the same query against this virtual table

Select PropertyID
From (Select Distinct PropertyID, FeatureID
            From tblproperty2feature)TPF
Group By PropertyID
Having Sum(Case When FeatureID In (1,4,5)
        Then 1 Else 0 End ) =  3

Otherwise, you'll have to use Exists, as BP suggested...


Quote:
> Sorry to cause confusion, it's tricky to explain,

> Your solution almost had it.

> This is my statement after reading your post

>  select Distinct(tp.propertyid )
>   from tblproperty tp
>    where exists (select * from  tblproperty2feature where featureid =  1)
>   AND exists (select * from  tblproperty2feature where featureid =  4)
>   AND exists (select * from  tblproperty2feature where featureid =  5)

> This stll brought back every propertyid because every property had atleast
> one of these features and
> the exists statements always would execute true.

> I only want to return the properties that contain all of these features.

> Thanks for your help anyway, much appreciated,

> Sasha



> > Sasha,

> > I have to admit to some confusion. So I'm guessing that the following is
> > what you want. Sorry if my confusion causes me to post a query that
> doesn't
> > meet your needs.

> > select *
> > from <table>
> > where exists (select * from t where PropertyId = 1)
> > and   exists (select * from t where PropertyId = 2)
> > and   exists (select * from t where PropertyId = 3)
> > and   exists (select * from t where PropertyId = 4)
> > and   exists (select * from t where PropertyId = 5)



> > > select fields from table where table.propertyid in (1,2,3,4,5)

> > > This statement works fine if i only have one propertyid what if I have
> > > multiple and only want to return the fields which match all the cases
in
> > my
> > > in statement?

> > > I can't use where propertyid = 1 and propertyid = 2 and propertyid = 3
> and
> > > propertyid = 4 and propertyid = 5 because this will always bring back
no
> > > result.

> > > An in works like an 'or' statement, I need an in that works like an
> 'and'.

> > > Is this possible?

> > > Thanks,
> > > Sasha



Wed, 24 Apr 2002 03:00:00 GMT
 IN statement query
Sasha,

Try:

select Distinct(tp.propertyid )
from tblproperty tp
where exists (select * from  tblproperty2feature as tp2 where tp2.featureid
=  1 and tp.PK = tp2.FK)
AND exists (select * from  tblproperty2feature as tp2 where tp2.featureid =
4 and tp.PK = tp2.FK)
AND exists (select * from  tblproperty2feature as tp2 where tp2.featureid =
5 and tp.PK = tp2.FK)

WHERE EXISTS only works as a correlated subquery, so you must use it that
way. You have to have some columns or columns that relate the rows in
tblproperty to tblproperty2feature. I am assuming that you have a primary
key / foreign key relationship (either explicitly or implicitly between the
two tables) and that you can match on that relationship.

If you are still having difficulties getting the above to work, kindly post
the table schemas plus some sample data to illustrate the table
relationships.


Quote:
> Sorry to cause confusion, it's tricky to explain,

> Your solution almost had it.

> This is my statement after reading your post

>  select Distinct(tp.propertyid )
>   from tblproperty tp
>    where exists (select * from  tblproperty2feature where featureid =  1)
>   AND exists (select * from  tblproperty2feature where featureid =  4)
>   AND exists (select * from  tblproperty2feature where featureid =  5)

> This stll brought back every propertyid because every property had atleast
> one of these features and
> the exists statements always would execute true.

> I only want to return the properties that contain all of these features.

> Thanks for your help anyway, much appreciated,

> Sasha



> > Sasha,

> > I have to admit to some confusion. So I'm guessing that the following is
> > what you want. Sorry if my confusion causes me to post a query that
> doesn't
> > meet your needs.

> > select *
> > from <table>
> > where exists (select * from t where PropertyId = 1)
> > and   exists (select * from t where PropertyId = 2)
> > and   exists (select * from t where PropertyId = 3)
> > and   exists (select * from t where PropertyId = 4)
> > and   exists (select * from t where PropertyId = 5)



> > > select fields from table where table.propertyid in (1,2,3,4,5)

> > > This statement works fine if i only have one propertyid what if I have
> > > multiple and only want to return the fields which match all the cases
in
> > my
> > > in statement?

> > > I can't use where propertyid = 1 and propertyid = 2 and propertyid = 3
> and
> > > propertyid = 4 and propertyid = 5 because this will always bring back
no
> > > result.

> > > An in works like an 'or' statement, I need an in that works like an
> 'and'.

> > > Is this possible?

> > > Thanks,
> > > Sasha



Wed, 24 Apr 2002 03:00:00 GMT
 IN statement query
Sorry to cause confusion, it's tricky to explain,

Your solution almost had it.

This is my statement after reading your post

 select Distinct(tp.propertyid )
  from tblproperty tp
   where exists (select * from  tblproperty2feature where featureid =  1)
  AND exists (select * from  tblproperty2feature where featureid =  4)
  AND exists (select * from  tblproperty2feature where featureid =  5)

This stll brought back every propertyid because every property had atleast
one of these features and
the exists statements always would execute true.

I only want to return the properties that contain all of these features.

Thanks for your help anyway, much appreciated,

Sasha


Quote:
> Sasha,

> I have to admit to some confusion. So I'm guessing that the following is
> what you want. Sorry if my confusion causes me to post a query that
doesn't
> meet your needs.

> select *
> from <table>
> where exists (select * from t where PropertyId = 1)
> and   exists (select * from t where PropertyId = 2)
> and   exists (select * from t where PropertyId = 3)
> and   exists (select * from t where PropertyId = 4)
> and   exists (select * from t where PropertyId = 5)



> > select fields from table where table.propertyid in (1,2,3,4,5)

> > This statement works fine if i only have one propertyid what if I have
> > multiple and only want to return the fields which match all the cases in
> my
> > in statement?

> > I can't use where propertyid = 1 and propertyid = 2 and propertyid = 3
and
> > propertyid = 4 and propertyid = 5 because this will always bring back no
> > result.

> > An in works like an 'or' statement, I need an in that works like an
'and'.

> > Is this possible?

> > Thanks,
> > Sasha



Thu, 25 Apr 2002 03:00:00 GMT
 IN statement query
Thanks BPMargolin, You have solved it for me.
That was fantastic. I owe you one.

Sasha


Quote:
> Sasha,

> Try:

> select Distinct(tp.propertyid )
> from tblproperty tp
> where exists (select * from  tblproperty2feature as tp2 where
tp2.featureid
> =  1 and tp.PK = tp2.FK)
> AND exists (select * from  tblproperty2feature as tp2 where tp2.featureid
=
> 4 and tp.PK = tp2.FK)
> AND exists (select * from  tblproperty2feature as tp2 where tp2.featureid
=
> 5 and tp.PK = tp2.FK)

> WHERE EXISTS only works as a correlated subquery, so you must use it that
> way. You have to have some columns or columns that relate the rows in
> tblproperty to tblproperty2feature. I am assuming that you have a primary
> key / foreign key relationship (either explicitly or implicitly between
the
> two tables) and that you can match on that relationship.

> If you are still having difficulties getting the above to work, kindly
post
> the table schemas plus some sample data to illustrate the table
> relationships.



> > Sorry to cause confusion, it's tricky to explain,

> > Your solution almost had it.

> > This is my statement after reading your post

> >  select Distinct(tp.propertyid )
> >   from tblproperty tp
> >    where exists (select * from  tblproperty2feature where featureid =
1)
> >   AND exists (select * from  tblproperty2feature where featureid =  4)
> >   AND exists (select * from  tblproperty2feature where featureid =  5)

> > This stll brought back every propertyid because every property had
atleast
> > one of these features and
> > the exists statements always would execute true.

> > I only want to return the properties that contain all of these features.

> > Thanks for your help anyway, much appreciated,

> > Sasha



> > > Sasha,

> > > I have to admit to some confusion. So I'm guessing that the following
is
> > > what you want. Sorry if my confusion causes me to post a query that
> > doesn't
> > > meet your needs.

> > > select *
> > > from <table>
> > > where exists (select * from t where PropertyId = 1)
> > > and   exists (select * from t where PropertyId = 2)
> > > and   exists (select * from t where PropertyId = 3)
> > > and   exists (select * from t where PropertyId = 4)
> > > and   exists (select * from t where PropertyId = 5)



> > > > select fields from table where table.propertyid in (1,2,3,4,5)

> > > > This statement works fine if i only have one propertyid what if I
have
> > > > multiple and only want to return the fields which match all the
cases
> in
> > > my
> > > > in statement?

> > > > I can't use where propertyid = 1 and propertyid = 2 and propertyid =
3
> > and
> > > > propertyid = 4 and propertyid = 5 because this will always bring
back
> no
> > > > result.

> > > > An in works like an 'or' statement, I need an in that works like an
> > 'and'.

> > > > Is this possible?

> > > > Thanks,
> > > > Sasha



Thu, 25 Apr 2002 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Is it possible to use MDX Statements/Queries in QUERY Analyzer(SQL Server)

2. SQL Statement Query Question

3. IN statement query

4. View vs. Statement Query Plan

5. SQL statement (query)

6. Select statement query

7. PRINT statement - Query analyser - not working

8. SQL7 bug-2nd query in a T-SQL statement with a compute statement loses headers

9. Query with executing dynamic sql statements and returning result to variable

10. Print statement in Query Analyzer...

11. SQL Query using hte "like" statement

12. Query in using variable in UPDATE statement


 
Powered by phpBB® Forum Software