CODEWITHSUNDEEP
sqldatabasepostgresql
Chankey Pathak
Chankey Pathak

Reputation: 21666

Select that column which don't have certain row

I have a table like below

   Column   |           Type           | Modifiers
------------+--------------------------+-----------
 id         | integer                  | not null
 number     | integer                  | not null
 status     | integer                  |
 uid        | integer                  |
 value      | integer                  |
 comment    | character varying(2000)  |
 date       | timestamp with time zone |

Query: select * from table_name where id like '%58943';

Result:

        id| number|     status |     uid |       value| comment  | date
----------+-------+------------+---------+------------+----------+-------------------------------
    58943 |     5 |          1 |       1 |            |          | 2014-01-23 14:24:34.708676+01
    58943 |     3 |          0 |       1 |          1 |          | 2014-01-23 14:23:46.740663+01
    58943 |     3 |          0 |       1 |          4 | [admin]  | 2014-01-23 14:24:34.505752+01
    58943 |     3 |          0 |     974 |          4 | [admin]  | 2014-01-23 14:24:34.601017+01
    58943 |     3 |          0 |     977 |          4 | [admin]  | 2014-01-23 14:24:34.708676+01
    58943 |     2 |          0 |       1 |            | ver 12   | 2014-01-23 14:22:01.298001+01
    58943 |     1 |          0 |       1 |            |          | 2014-01-23 14:22:01.052535+01
(7 rows)

Query: select * from table_name where id like '%58944';

Result:

            id| number|     status |     uid |       value| comment  | date
----------+-------+------------+---------+------------+----------+-------------------------------
        58944 |     5 |          1 |       1 |            |                   | 2014-01-23 14:25:34+01
        58944 |     3 |          0 |     977 |          4 | looks fine        | 2014-01-23 14:25:34+01
        58944 |     3 |          0 |     974 |          4 | Approve all conff | 2014-01-23 14:25:34+01
        58944 |     2 |          0 |       1 |            | vers 12           | 2014-01-23 14:22:11.86668+01
        58944 |     1 |          0 |       1 |            |                   | 2014-01-23 14:22:11.857947+01
    (5 rows)

Question: I'm trying to find those id which have 5| 1| 1 (number| status| uid)

but don't have 3| 0| 1| 1 (number| status| uid| value).

So if I run that query on complete table then I should get only 58944 in result.

Upvotes: 0

Views: 88

Answers (5)

listonator
listonator

Reputation: 1

Misunderstood in my comment above. This is easy:

SELECT 
  id 
FROM 
  table_name 
WHERE 
(number = 5 AND status = 1 AND uid = 1) 
and 
id not in (select id from table_name where number = 3 AND status = 0 AND uid = 1 and VALUE =1)

Upvotes: 0

user2989408
user2989408

Reputation: 3137

This below query should work for you.

SELECT id FROM table_name 
WHERE (number = 5 AND status = 1 AND uid = 1) 
    AND NOT (number = 3 AND status = 0 AND uid = 1 AND value = 1)

EDIT : I think I might have misunderstood your question. The following query will get you ids that have 5| 1| 1 (number| status| uid) but not 3| 0| 1| 1 (number| status| uid| value). 

SELECT * FROM tab a 
WHERE (a.number = 5 AND a.status = 1 AND a.uid = 1) 
    AND NOT EXISTS (SELECT id FROM tab t 
                    WHERE t.id = a.id AND t.number = 3 AND t.status = 0 
                        AND t.uid = 1 AND t.value = 1)

Check this fiddle outSQL FIDDLE

Upvotes: 2

Pavan
Pavan

Reputation: 33542

Modifying user2989408 answer to the below could work.

SELECT id FROM table_name 
WHERE (id  like '%58943' AND number = 5 AND status = 1 AND uid = 1) 
AND NOT (number = 3 AND status = 0 AND uid = 1 AND value = 1)

Upvotes: 0

Tomas Greif
Tomas Greif

Reputation: 22623

You need to use not exists:

select
  a.*
from
  table_name a
where
  (number = 5 and status = 1 and uid = 1)
  and not exists (
    select 1
    from 
      table_name b
    where 
      b.id = a.id
      and (b.number = 3 and b.status = 0 and b.uid = 1)
  )

Upvotes: 0

listonator
listonator

Reputation: 1

Or just:

SELECT id FROM table_name 
WHERE (number = 5 AND status = 1 AND uid = 1)

I don't see the need to exclude the 3|0|1|1 when the inclusion criteria of 5|0|1 is specific and exclusive...

Upvotes: 0

Related Questions