MySQL – Listing Rows with WHERE Condition

MySQLmysql-5.5select

I am struggling with a SQL query that lists hosts that are offline.

Problem

The problem occurs when I need to list hosts that don't have any ports open (status = 0), so looking at the table at the bottom it should just list 192.168.1.2, however it lists all of the hosts.

I have tried many queries + sub queries with no luck yet, I would be grateful if you can tell me where I am going wrong and let me know what the correct query is. Thank you.

MariaDB [scanner]> SELECT DISTINCT ports.ip_add FROM ports WHERE ports.status = FALSE;
+-------------+
| ip_add      |
+-------------+
| 192.168.1.1 |
| 192.168.1.2 |
| 192.168.1.3 |
+-------------+

Similar Working Query

This query lists all hosts that have a least one port open (status = 1), great.

MariaDB [scanner]> SELECT DISTINCT ports.ip_add FROM ports WHERE ports.status = TRUE;
+-------------+
| ip_add      |
+-------------+
| 192.168.1.1 |
| 192.168.1.3 |
+-------------+

Table

MariaDB [scanner]> SELECT * FROM ports LIMIT 9;
+--------+-------------+----------+------------+---------------------+
| id     | ip_add      | port     | status     | probe_meta          |
+--------+-------------+----------+------------+---------------------+
|      1 | 192.168.1.1 |       22 |          1 | 2016-03-29 00:01:00 |
|      2 | 192.168.1.1 |       21 |          1 | 2016-03-29 00:02:00 |
|      3 | 192.168.1.1 |       23 |          1 | 2016-03-29 00:03:00 |
|      4 | 192.168.1.2 |       22 |          0 | 2016-03-29 00:05:00 |
|      5 | 192.168.1.2 |       21 |          0 | 2016-03-29 00:06:00 |
|      6 | 192.168.1.2 |       23 |          0 | 2016-03-29 00:07:00 |
|      7 | 192.168.1.3 |       22 |          1 | 2016-03-29 00:09:00 |
|      8 | 192.168.1.3 |       21 |          0 | 2016-03-29 00:10:00 |
|      9 | 192.168.1.3 |       23 |          0 | 2016-03-29 00:11:00 |
+--------+-------------+----------+------------+---------------------+

Best Answer

One other way of doing this would be to aggregate the status column by ip_addr, where the total is equal to zero.

The test-bed:

CREATE TABLE `ports`
(
    `id` INT NOT NULL
    , `ip_add` VARCHAR(15) NOT NULL
    , `port` INT NOT NULL
    , `status` BIT NOT NULL
    , `probe_meta` DATETIME
);

INSERT INTO `ports` 
VALUES (1, '192.168.0.1', 22, 1, '2016-03-21 00:00:00');

INSERT INTO `ports` 
VALUES (1, '192.168.0.2', 22, 0, '2016-03-21 00:01:00');

INSERT INTO `ports` 
VALUES (1, '192.168.0.3', 22, 1, '2016-03-21 00:02:00');

INSERT INTO `ports` 
VALUES (1, '192.168.0.3', 22, 0, '2016-03-21 00:03:00');

The query:

SELECT ip_add
FROM `ports`
GROUP BY ip_add
HAVING SUM(status) = 0;

The results:

SQLFiddle

Related Solutions

Sql-server – How to we automatically replace a table with a view for a specific user

You can check these things in sys.dm_exec_sessions, but you have to do it in your (let's hope) stored procedures (you can't redirect a query that specifies a specific table name to a different table based on these variables):

ALTER PROCEDURE dbo.report
AS
BEGIN
  SET NOCOUNT ON;

  IF EXISTS 
  (
    SELECT 1 FROM sys.dm_exec_sessions 
      WHERE program_name = 'program' 
      AND login_name = 'user'
      AND session_id = @@SPID
  )
  BEGIN
    -- do it their way
    RETURN;
  END

-- do it the normal way
END
GO

This is a bad solution though. If one user wants custom ordering, what happens when another user decides they, too, want different ordering? It will happen. Now all your reports are going to have three forks? Why not make ORDER BY a part of the interface?

I'm wondering if you could do something here with schemas, for example if this guy has a different default schema you could write the query such that it doesn't reference a schema. Everyone else will get dbo.viewname and he would get newschema.viewname. But this is messy, the opposite of self-documenting, and if you're going to write code that handles his case, you may as well fix the code the right way.

Plus, as I suggested in my comment, simply referencing a different view or table name doesn't guarantee you'll get a different ordering by doing so. SQL Server still only has to obey a specific order if you've actually stated one in the outer ORDER BY.

Mysql – Avoiding Multiple Queries when Searching for Records Associated with a Set of Records

If I understand you correctly, you want to display the products regardless if they are associated with a price rule, right ? In my opinion, you'd best achieve this with an OUTER JOIN, which will give you all products and simply return an empty PriceRuleId column for the products without price rules.

The following query will give you a result Matrix with all products and their price rules, if they have them.

select 
     p.id as ProductId
    ,pr.id as PriceRuleId
    ,pr.apply_to as PriceRuleApplyTo
    ,pr.start_date as PriceRuleStartDate
    ,pr.end_date as PriceRuleEndDate
    ,pr.promo_code as PriceRulePromoCode
from
    products p
    LEFT OUTER JOIN price_rule_product prp ON prp.product_id = p.id
    LEFT OUTER JOIN
        ( SELECT id, apply_to, start_date, end_date, promo_code
            FROM  price_rule
            WHERE 
                apply_to = 'line_items'
                AND ( start_date IS NULL OR start_date = 0 OR start_date <= 1366063902 )
                AND ( end_date IS NULL OR end_date = 0 OR end_date >= 1366063902 )
                AND ( promo_code IS NULL OR promo_code = '' )
            ) pr ON prp.price_rule_id = pr.id

I hope I understood the problem correctly.

Best Answer

Related Solutions

Sql-server – How to we automatically replace a table with a view for a specific user

Mysql – Avoiding Multiple Queries when Searching for Records Associated with a Set of Records

Related Question