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 |
|
wildatom
Starting Member
2 Posts |
 Posted - 2009-06-10 : 15:22:43
|
| My table:ID | LOCATION__ |___________1f | 4th_Floor1f | 4th_Floor1f | 3rd_Floor1f | 1st_Floor 2f | 4th_Floor2f | 4th_Floor3f | 6rd_Floor4f | 4th_Floor4f | 4th_Floor4f | 3rd_Floor4f | 1st_Floor I need to find out what Unit_ID's are on 3rd_Floor but are not on the 4th_Floor. As you can see 3f is not on the 4th_Floor. So query would list a result of 3f. I'm not sure how to takle this, I've tried different table views, but was looking for a simple query to do the job.Thank You |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-06-10 : 15:40:13
|
Try this:SELECT IDFROM YourTableWHERE LOCATION = '3rd_Floor'AND ID NOT IN (SELECT ID FROM YourTable WHERE LOCATION = '4th_Floor') Some days you're the dog, and some days you're the fire hydrant. |
 |
|
|
wildatom
Starting Member
2 Posts |
Posted - 2009-06-10 : 15:51:16
|
| Worked like a charm. I write queries all the time, and yet I couldn't figure this out. Thank you soooooo much! Glad I joined, maybe I can help someone out. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-10 : 15:56:34
|
[code]SELECT IDFROM Table1GROUP BY IDHAVING MAX(CASE Location WHEN '4th_Floor' THEN 2 WHEN '3rd_Floor' THEN 1 ELSE 0 END) = 1[/code]
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-06-10 : 15:56:40
|
| You're welcome :)Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
|
|
|
Compare Records with Mutliple P_ID