Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dobly
Starting Member
16 Posts |
 Posted - 2010-08-26 : 02:07:58
|
| I have a simple database with 2 tables. LocationsPK LocationIDActivityPK ActivityIDFK LocationID Status (int)As you can see, one location can have many Activities. What I need to do is a query that returns all the locations along with a Status value:If none of a Location's Activities has a status of 1, I need to return Status of 0 with that LocationIf ANY one of that Location's Activities has a status of 1, I need to return Status of 1 with that Location. (Basically saying, 'this location has at least one activity with a Status of 1')It's doing my head in. I must be able to do this in one query. Any help is would be much appreciated. |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2010-08-26 : 02:43:29
|
| Do you need this?Select LocationID,min(Status ) as status from Activitygroup by LocationIDSenthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-08-26 : 03:59:50
|
Senthil, replace MIN() with MAX().
N 56°04'39.26"E 12°55'05.63" |
|
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2010-08-26 : 06:38:44
|
quote:
Originally posted by Peso
Senthil, replace MIN() with MAX().
N 56°04'39.26"E 12°55'05.63"
Ya fine, i understood viceversa Thanks Peso.Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
|
|
|
Dobly
Starting Member
16 Posts |
Posted - 2010-08-26 : 18:53:08
|
| Awesome. Works a treat. Thanks Peso |
|
|
|
|
|
|
Where the status = 1