how to check child table all column values are negative in sql

by

I would use a sub-query get the FK ids of the entries that has positives values, and then use the result of that query to filter in the main query using a WHERE NOT IN clause

Schema (MySQL v5.7)

CREATE TABLE A (
  `id` INTEGER PRIMARY KEY
);

INSERT INTO A
  (`id`)
VALUES
  (1),
  (2),
  (3);

CREATE TABLE B (
  `id` INTEGER,
  `value` INTEGER,
  `fk_a` INTEGER,
  FOREIGN KEY (fk_a) REFERENCES A (id)
);

INSERT INTO B
  (`id`, `value`, `fk_a`)
VALUES
  (1, -5, 1),
  (2, -7, 1),
  (3, 5, 2),
  (4, -10, 3),
  (5, 20, 3);

Query #1

SELECT * FROM A
WHERE A.id NOT IN
(
  SELECT tb.fk_a
  FROM B tb
  WHERE tb.`value` >= 0
);

Output

| id  |
| --- |
| 1   |

View on DB Fiddle

More Related Contents:

  1. Insert local .csv data in remote SQL Server via already working connection string
  2. Get previous month in SQL when given a varchar
  3. How to retrieve a specific users’ data from SQL Server?
  4. How to generate a range of numbers between two numbers?
  5. T-SQL dynamic pivot
  6. Select group of rows that match all items in a list
  7. Is there any difference between IS NULL and =NULL
  8. ERROR: there is no unique constraint matching given keys for referenced table “bar”
  9. SQL statement to select all rows from previous day
  10. Top n records per group sql in access
  11. What is the order of execution for this SQL statement
  12. How to import text files with the same name and schema but different directories into database?
  13. ‘^M’ character at end of lines
  14. How to use SQL Order By statement to sort results case insensitive?
  15. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  16. How do you version your database schema? [closed]
  17. SQL : in clause in stored procedure:how to pass values
  18. Best way to do nested case statement logic in SQL Server
  19. Get month from DATETIME in sqlite
  20. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  21. How to count the number of occurrences of a character in an Oracle varchar value?
  22. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  23. How to use multiple CTEs in a single SQL query?
  24. How to delete duplicate rows with SQL?
  25. what is the difference between GROUP BY and ORDER BY in sql
  26. Best way to test SQL queries [closed]
  27. sql query if parameter is null select all
  28. How to write a WHERE Clause to find all records in a specific month?
  29. Insert data and set foreign keys with Postgres
  30. How to check db2 version

Leave a Comment