SQL Server Performance Forum – Threads Archive

Between two columns

Select top 5000 ClientID From tblClients, tblRange Where tblClients.ClientID not between tblRange.[Lower] AND tblRange.[Upper] I want to select the top X records from my table where there ID’s are not between any of the numbers specified between the two columns…does that make sence? Now it works (kind of) but it only compares the ClientID with the first row in tblRange. I have multiple rows and I want the clause to check all of them.. tblRange:
[Lower], [Upper]
1, 1000
5000, 6000 Any input would be appreciated,
Brian

---

I think this will work Select top 5000
c1.ClientID
From tblClients c1
where c1.ClientID not in (
select distinct
c2.ClientID
From tblClients c2,
tblRange r
Where
c2.ClientID between r.[Lower] AND r.[Upper]
) Cheers
Twan

---

This still only selects ClientID’s from the first row in tblRange…

---

I found a solution…I was looking for something like this: SELECT TOP 5000
C.clientid
FROM tblClients AS C
LEFT JOIN tblRange AS R
ON C.clientid BETWEEN R.[lower] AND R.[upper]
GROUP BY C.clientid
HAVING COUNT(R.[lower])=0
ORDER BY C.clientid OR SELECT TOP 5000
clientid
FROM tblClients AS C
WHERE NOT EXISTS
(SELECT *
FROM tblRange
WHERE C.clientid BETWEEN [lower] AND [upper])
ORDER BY clientid

---

]]>