Select Ids from Multiple Rows Where Column Values Satisfy One Condition But Not Another

Select IDs from multiple rows where column values satisfy one condition but not another

SELECT FirstID
FROM table
WHERE SecondId in (1,2,3) --Included values
AND FirstID NOT IN (SELECT FirstID FROM test
WHERE SecondId IN (4,5)) --Excluded values

Return an ID when the ID has conditions in multiple rows that have to be met

This is what you want:

select ID, EVENT, STATUS
from EOMSCHEMA.AUDIT_RECORDS
where event = 'Activate' and status = 'Completed'
and ID IN(SELECT ID FROM EOMSCHEMA.AUDIT_RECORDS WHERE event = 'Create' and status= 'Completed')

Select IDs which satisfy a condition in each repetition

This seems like a good use case for groupby.
Here are a couple of examples:

df.groupby("ID").apply(lambda df: (df.Status == 100).all())

This gives a boolean status for each ID:

ID
A True
B False
C True
D False

Another option if the mean can be relied upon:

df.groupby("ID").mean().pipe(lambda df: df[df.Status == 100])

This gives the result:

ID  Status
A 100.0
C 100.0

MySQL Select ID's which occur on different rows with multiple specific values for a column

Your expression in a WHERE clause works against a single row of the joined result set. That's why WHERE category_id = 201 AND category_id = 202 doesn't work -- because it can't be two values on a single row.

So you need some way to join two rows from the table into one row of the result set. You can do this with a self-join:

SELECT c1.item_id
FROM item_category AS c1
INNER JOIN item_category AS c2 ON c1.item_id = c2.item_id
WHERE c1.category_id = 201 AND c2.category_id = 202

This technique is hard to scale up when you want to search for three, four, five or more values, because it requires N-1 joins to match N values.

So another method is to use GROUP BY:

SELECT c.item_id, COUNT(*) AS cat_count
FROM item_category AS c
WHERE c.category_id IN (201,202)
GROUP BY c.item_id
HAVING cat_count = 2

Both techniques are okay, and work better in different circumstances.

SQL: subset data: select id when time_id for id satisfy a condition from another column

This query:

select id from tablename where time_id = 5

returns all the ids that you want in the results.

Use it with the operator IN:

select *
from tablename
where id in (select id from tablename where time_id = 5)

Mysql: select value that matches several criteria on multiple rows

This should work:

SELECT DISTINCT (T1.ID), T1.DOC, T2.AUTH FROM T1 
LEFT JOIN T2 ON T2.ID = T1.ID
WHERE T1.DOC IN( SELECT T1.DOC FROM T2
LEFT JOIN T1 ON T1.ID = T2.ID
WHERE T2.AUTH IN('EP','US')
GROUP BY T1.DOC HAVING COUNT(DISTINCT T2.AUTH) = 2) ;

SQL check id value from multiple rows

You can get the list of ids using group by and having. An exact translation of your conditions is:

select id
from t
group by id
having count(*) > 1 and
sum( foo = 0 and bar = 'yes' ) = 1 and
sum( bar = 'no' ) = count(*) - 1;

You can use a subquery if you want the count instead of the list.

Select IDs with different value in another column in a specific sequence

With EXISTS:

select id from table1 t 
where flag = 1
and exists (
select 1 from table1 where id = t.id and flag = 0 and dates > t.dates
)

See the demo

To get full rows:

select * from table1 where id in (
select id from table1 t
where flag = 1
and exists (
select 1 from table1 where id = t.id and flag = 0 and dates > t.dates
)
)

See the demo

Or with a UNION:

select * from table1 t 
where flag = 1
and exists (
select 1 from table1 where id = t.id and flag = 0 and dates > t.dates
)
union all
select * from table1 t
where flag = 0
and exists (
select 1 from table1 where id = t.id and flag = 1 and dates < t.dates
)
order by id, dates

See the demo

And one more:

select * from table1 t 
where exists (
select 1 from table1
where id = t.id
and 1 - flag = abs(t.flag)
and (t.flag = 1 and dates > t.dates) or (t.flag = 0 and dates < t.dates)
)

See the demo



Related Topics



Leave a reply



Submit