Author 
Message 
BPMargoli #1 / 6

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 


Lewis Bruc #2 / 6

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 


Charles Bretan #3 / 6

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 


BPMargoli #4 / 6

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 


Sasha Shevele #5 / 6

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 


Sasha Shevele #6 / 6

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 


