CODEWITHSUNDEEP
mysqlsqlmariadb
BinaryDebug
BinaryDebug

Reputation: 70

SQL get rows from table unless a value exists

enter image description here

Trying to build a select query.

This is my table, let's call the first column 'id', second column 'two', and third column 'three', and final column 'date_time'

I have this table. I want to get all the rows equal to 7 (on column 'three'), unless there's an entry equal to 8 (on column 'three').

For example I want the row with id 6 but NOT id 8 (as another row has 8 inputted)

I was able to get the rows but as soon as another entry with 8 exists, it doesn't return anything at all (using not exists)

Thanks.

Upvotes: 0

Views: 180

Answers (5)

Sujit Verma
Sujit Verma

Reputation: 180

You can use self join to get your result

select A.* from table_name A left join table_name B on A.id = B.three 
where A.three = 7 and B.three is null;

In the above query it will get all the rows from table A where column 'three' having value 7 then it will assign null to column 'three' of table B where column 'id' of A table not found in column 'three' of B table.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You may use exists as

   select *
     from tab t1
    where t1.three = 7
      and exists ( select 1 from tab t2 where t2.id = t1.id and t1.id != 8 );

   id   two  three date_time
   ---  ---- ----- -------------------
    6    4     7   2018-12-17 16:56:41

Rextester Demo

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

We can do this via an EXISTS query:

SELECT id, two, three, date_time
FROM yourTable t1
WHERE three = 7 AND NOT EXISTS (SELECT 1 FROM yourTable t2 WHERE t2.three = t1.id);

enter image description here

Demo

This approach would probably outperform an anti-join.

Upvotes: 1

João Santos
João Santos

Reputation: 43

If I understood your question correctly, I believe the below code would solve your problem. 

SELECT 
  A.ID, A.TWO, A.THREE, A.DATE_TIME 
FROM 
  TABLE A
LEFT JOIN 
  TABLE B ON A.TWO=B.TWO AND B.THREE=8 AND A.ID<>B.ID
WHERE 
   B.ID IS NULL
   AND A.THREE=7

Upvotes: 0

ADyson
ADyson

Reputation: 61859

The description of your requirement is not 100% clear, but I think you want to ask for those rows where column three = 7, but except for those rows where the ID of that row exists elsewhere in column three of the table.

This should work:

SELECT 
  `id`, `two`, `three`, `date_time`
FROM 
  `yourtable`
WHERE 
  `three` = 7
  AND `id` NOT IN (SELECT `three` FROM `yourtable`)

Upvotes: 0

Related Questions