Return Row Only If Value Doesn't Exist

Return row only if value doesn't exist

SELECT *
FROM reservation
WHERE id NOT IN (select reservation_id
                 FROM reservation_log
                 WHERE change_type = 'cancel')

OR:

SELECT r.*
FROM reservation r
LEFT JOIN reservation_log l ON r.id = l.reservation_id AND l.change_type = 'cancel'
WHERE l.id IS NULL

The first version is more intuitive, but I think the second version usually gets better performance (assuming you have indexes on the columns used in the join).

The second version works because LEFT JOIN returns a row for all rows in the first table. When the ON condition succeeds, those rows will include the columns from the second table, just like INNER JOIN. When the condition fails, the returned row will contain NULL for all the columns in the second table. The WHERE l.id IS NULL test then matches those rows, so it finds all the rows that don't have a match between the tables.

How to return a result as a column value if a row exists or not?

Use LEFT JOIN (outer join) and then check for the existence of tID in table2 by using of the IS NULL expression

Select t1.*, t2.tid is not null as ex 
from table1 t1
left join table2 t2 on t1.tid = t2.tid

demo

EDIT: "What if I wanted only the values where sID matches some value ?"

Select t1.*, t2.tid is not null as ex 
from table1 t1
left join table2 t2 on t1.tid = t2.tid and t2.sid = <some_value>

SQL - Only Return Row If Column Does Not Have Same Value

You can use not exists. If you only care about the first name:

select t.*
from test t
where not exists (select 1
                  from t t2
                  where t2.firstname = t.firstname and
                        t2.id <> t.id
                 );

Select rows where a certain value doesn't exist

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Data ( SESS_CODE, YEAR, "Count", ID ) AS
          SELECT 'D',           2014,    1,       51 FROM DUAL
UNION ALL SELECT 'W',           2014,    1,       51 FROM DUAL
UNION ALL SELECT NULL,          2014,    2,       51 FROM DUAL
UNION ALL SELECT 'O',           2014,    1,       52 FROM DUAL
UNION ALL SELECT 'W',           2014,    1,       52 FROM DUAL
UNION ALL SELECT NULL,          2014,    2,       52 FROM DUAL
UNION ALL SELECT 'D',           2014,    2,       53 FROM DUAL
UNION ALL SELECT 'O',           2014,    1,       54 FROM DUAL
UNION ALL SELECT 'W',           2014,    1,       55 FROM DUAL;

Query 1:

One analytical function and a filter (no joins):

WITH Max_Sess_Codes AS (
  SELECT SESS_CODE,
         YEAR,
         "Count",
         ID,
         MAX( SESS_CODE ) KEEP ( DENSE_RANK FIRST ORDER BY DECODE( SESS_CODE, 'D', 1, 0 ) ) OVER ( PARTITION BY ID ) AS max_sess_code
  FROM   Data d
)
SELECT SESS_CODE,
       YEAR,
       "Count",
       ID
FROM   Max_Sess_Codes
WHERE  max_sess_code = 'D'

Results:

| SESS_CODE | YEAR | COUNT | ID |
|-----------|------|-------|----|
|         D | 2014 |     2 | 53 |

Query 2:

If you have a composite UNIQUE constraint on SESS_CODE and ID (i.e. if for each ID there can only be one row with SESS_CODE of 'D') then you could use:

SELECT MAX( SESS_CODE ) AS SESS_CODE,
       MAX( year ) AS year,
       MAX( "Count" ) AS "Count",
       ID
FROM   Data
GROUP BY ID
HAVING COUNT( CASE SESS_CODE WHEN 'D' THEN NULL ELSE 1 END ) = 0

Results:

| SESS_CODE | YEAR | COUNT | ID |
|-----------|------|-------|----|
|         D | 2014 |     2 | 53 |

sql select row if another doesn't exist

Which tag is in alarm the longest is asking which tag has the oldest current alarm code.

You can do this with conditional aggregation:

select tagname, max(timestamp)
from t
group by tagname
having max(timestamp) = max(case when status = 'Alarm' then timestamp end)
order by max(timestamp) asc;

This assumes that two alarms are not in sequence for the same tag -- this is consistent with your described data.

POSTGRES: How to select rows with a certain value only if another value doesn't exist, and in that case select the other value?

You can use Row_number() function together with a case statement to identify which row to pick.

with cte AS
(
  select id,
       date, 
       type,
       row_number() over(partition by id 
                          order by case when type <> 'NONE' THEN 1 ELSE 2 END, date desc
                          ) as RN
       
  from test
 )
 select *
 from cte
 where rn = 1

SQL Fiddle

---

Related Topics