CODEWITHSUNDEEP
phpmysql
J. Moura
J. Moura

Reputation: 23

Search data who doesn´t appear between values

here is my problem,

Imagine this table

id| idcompany
----------
1 | 1
----------
2 | 2
----------
3 | 1
----------
5 | 1
----------
6 | 2

I did a code in PHP like this:

if id <= 2 then DAY1
if id BETWEEN 3,4 then DAY2
if id BETWEEN 5,6 then DAY3

Looking to the table we can assume that company 1 appears on Day1, Day2 and Day 3 and company 2 appears Day1 and Day3

What I want to accomplish here is: How can I SELECT in SQL all the companies who won't participate on Day 2?

I already tried:

SELECT * FROM tickets
WHERE id NOT BETWEEN 3 AND 4
GROUP BY(idcompany)

But its obvious that wont work because the Query will select the idcompany from another days and print on result.

Can someone help me to fig this out? Thanks in advance.

Upvotes: 2

Views: 51

Answers (3)

Alex
Alex

Reputation: 17289

Hope, I got you correctly

http://sqlfiddle.com/#!9/6f9921/1

SELECT idcompany
FROM tickets
GROUP BY idcompany
HAVING SUM(IF(id IN (3,4),1,0))=0

UPDATE If you need an interval you can replace condition in SUM:

SELECT idcompany
FROM tickets
GROUP BY idcompany
HAVING SUM(IF(id BETWEEN 330601 AND 40800,1,0))=0

Upvotes: 1

AdamMc331
AdamMc331

Reputation: 16690

To get a list of companies who didn't participate on day two, start by getting a list of those who did:

SELECT DISTINCT companyID
FROM myTable
WHERE id BETWEEN 3 AND 4;

Then you can exclude them from the final result by selecting all companies, and using a NOT IN operator to filter those out:

SELECT DISTINCT companyID
FROM myTable
WHERE companyId NOT IN(
  SELECT DISTINCT companyID
  FROM myTable
  WHERE id BETWEEN 3 AND 4);

Here is an SQL Fiddle example.

Upvotes: 1

Hiti3
Hiti3

Reputation: 172

Your table is rather confusing, I can't seem to find the information where Company 1 appears on Day 2.

But if I have understood correctly, here is a simple NOT or != operand for the WHERE operator example:

SELECT * FROM tickets
WHERE id != 3 AND id != 4

Upvotes: 0

Related Questions