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 id
s 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 id
s 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
Er_Access_Denied_Error: Access Denied for User ''@'Localhost' (Using Password: No)
Calling an API from SQL Server Stored Procedure
Extracting Data Between Two Delimiters in SQL Server
Checking a Column If It Contains a Row Value
I Want to Give Serial No in My Query According to Group
Concatenate With String But Exclude When Null
How to Subtract One Day from Current Date Then Convert to String in Hive
Sqlstate[Hy000] [2002] Php_Network_Getaddresses: Getaddrinfo Failed: Name or Service Not Known
Postgresql Convert Columns to Rows Transpose
What Is the Best Datatype for Storing Urls in a MySQL Database
How to Convert from Blob to Text in MySQL
How to Search Json Array in MySQL
Access Denied for Load Data Infile in MySQL
How to Split One Column into Two Columns in SQL Server
Find Employee Name Who Is Having Highest Salary in Given Month in SQL
Postgresql - Combining Multiple Rows With Several Attributes into One Row