CODEWITHSUNDEEP
sqlsql-server-2005
JoSav
JoSav

Reputation: 247

Sql Between Dynamic

I'm trying to do a sql query to get data in a clause in between. The problems, is that i dont know how to do a query who can have differences clause between depends on the value in the parameter.

What i have:

SELECT TableA.DNSName, TableA.IPAddressStr, TableB.OSName
FROM TableB 
INNER JOIN TableA 
  ON TableB.AssetOSID = TableA.AssetOSID 
CROSS JOIN IPRanges
WHERE (IpRanges.ConfigID = @configID)
  (Assets.IPAddress BETWEEN IPRanges.StartIPAddress AND IPRanges.EndIPAddress)

what table look like:

IpRanges:
ConfigID|StartIpAdress|EndIPAdress <br>
1|23|53
1|56|87
3|90|134
3|560|600 
3|780|800

As you can see the number of ipRanges changes on each configID and theres is no link between the table ipRanges And the table TableA.

Thanks in advance.

Upvotes: 1

Views: 60

Answers (2)

xQbert
xQbert

Reputation: 35353

Not exactly sure what you're after here.. Some sample results along with sample data from other tables would help..

But this may be close.

SELECT TableA.DNSName, TableA.IPAddressStr, TableB.OSName 
FROM TableB 
INNER JOIN TableA 
  ON TableB.AssetOSID = TableA.AssetOSID 
INNER JOIN IPRanges 
  ON (IpRanges.ConfigID = @configID)
 AND (Assets.IPAddress BETWEEN IPRanges.StartIPAddress AND IPRanges.EndIPAddress)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271171

In this case, you need to use aggregation with a having clause -- because you want all the conditions to be true.

The idea is to count the number of times the ranges are true and compare them to the total count:

SELECT TableA.DNSName, TableA.IPAddressStr, TableB.OSName
FROM TableB INNER JOIN
     TableA
     ON TableB.AssetOSID = TableA.AssetOSID CROSS JOIN
     IPRanges
WHERE (IpRanges.ConfigID = @configID)
group by TableA.DNSName, TableA.IPAddressStr, TableB.OSName
having sum(case when Assets.IPAddress BETWEEN IPRanges.StartIPAddress AND IPRanges.EndIPAddress) then 1 else 0 end) =
       count(*);

If only one of the rows needs to match, then use this having clause:

having sum(case when Assets.IPAddress BETWEEN IPRanges.StartIPAddress AND IPRanges.EndIPAddress) then 1 else 0 end) > 0

Upvotes: 1

Related Questions