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 |
|
twscross
Starting Member
2 Posts |
 Posted - 2012-02-29 : 10:25:01
|
| I have a query I want to write but I can't seem to get it right. I have a table with CustomerID and AppVersion fields (amongst others). I want to find all the CustomerIDs where they have an entry for one AppVersion but not another (the AppVersion field gets appended to each time the customer upgrades their software).Therefore I want all CustomerIDs where the table contains 1.1.0 but NOT 1.1.1.I've been looking around for similar examples and trying various thinds / trying to adapt queries but I'm struggling. Below is as close as I have come:SELECT DISTINCT CustomerID, AppVersion FROM MyTableWHERE AppVersion NOT LIKE '1.1.1%'AND AppVersion IN ( SELECT AppVersion FROM MyTable WHERE AppVersion IN ( SELECT AppVersion FROM MyTable WHERE AppVersion LIKE '1.1.0%') )ORDER BY CustomerID ASCHowever, when I look further into the records for some of the resulting CustomerIDs, I find that they do have AppVersions of 1.1.1 (which is what I'm trying to exclude).Obviously this is wrong. Can anyone help point me in the right direction?Thanks in advance.Tim |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-02-29 : 10:29:52
|
select * from table t1where AppVersion like '1.1.0%'and not exists(select * from table t2 where t2.CustomerID=t1.CustomerID and AppVersion like '1.1.1%')
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
twscross
Starting Member
2 Posts |
Posted - 2012-02-29 : 10:42:40
|
Spot on, thank you so much  |
|
|
|
|
|
|
Problems with nested selects...