CODEWITHSUNDEEP
sqlsql-server
Szymon Toda
Szymon Toda

Reputation: 4516

SQL query for selecting range of IP and name

How to select records that have one of given IP and name of "admin"?

I have this for IPs but feel that is not the best way of grouping range of it. As stated in comments I want to select "name" which could have IP: "x" or "y" or "z"

SELECT DISTINCT (name) AS name
FROM  `log` 
WHERE  `ip` =  '08.88.16.98'
OR  `ip` =  '03.12.27.226'
OR  `ip` =  '03.23.113.116'
OR  `ip` =  '06.204.69.183'
OR  `ip` =  '07.31.0.209'

Upvotes: 0

Views: 76

Answers (2)

Yair Nevet
Yair Nevet

Reputation: 13003

Use the IN clause instead of the OR statements:

SELECT DISTINCT (name) AS name
FROM  log 
WHERE ip
IN ('08.88.16.98','03.12.27.226','03.23.113.116','06.204.69.183','07.31.0.209')
AND name = 'admin';

The IN operator allows you to specify multiple values in a WHERE clause.

Just as a side note, you can use the NOT clause before the IN clause if you want to excludes values, like this, for example:

NOT IN ('08.88.16.98','03.12.24.254','03.23.113.189');

Read here more about it: IN (Transact-SQL)

Upvotes: 1

Farvardin
Farvardin

Reputation: 5424

you can add a new table for your desired ip address. give it ips. then join two tables:

SELECT DISTINCT (name) AS name
FROM  `log` 
JOIN `ips` ON (log.ip = ips.ip);

Upvotes: 0

Related Questions