I have a SQL Server table of Customer’s Events: There can be many EventTypes for the same customer in one day. EventTypes are like 1 – CheckIn 2 – CheckOut 3 – ExamStart 4 – ExamEnd Now I want to select Customers that are currently (today) on premises. That’s Clients who ha…
Tag: subquery
Mysql how to select only row in same table and same identifier but different value
I have this dataset : UserPlans table : how to only select this row (with ID 3) since this data has been deleted but doesn’t have any data with deletedAt = NULL with same userId ? Answer You seem to want rows where userid has no other row whose deletedAt is null. If so, you can use not exists and
If Row from left joined table is null use another row
I have two table that I am trying to join(see below). Each have the same four columns on which I need to join. The second is the complete list, while the first may be missing some records. Where the records are missing I want to use the lowest ROWNUM that matches the other three. I am failing to see a
One-to-Many SQL SELECT concatenated into single row
I’m using Postgres and I have the following schemes. Orders Comments So, in this case, an order can have many comments. I need to iterate over the orders and get something like this: I tried to use LEFT JOIN but it didn’t work it returns this: Answer You are almost there – you just need aggr…
Left Join – attempting to identify instances where child records do not exist, or if they do exist, populate unique records meeting specific criteria
I am attempting to search an SQL database for instances where child records do not exist, or if they do exist, they are a specific type (e.g. Historical) and no other types exist (e.g. Current): This populates all of my parent records that do not have any child records without any issues. However, I also want…
Update column in Oracle table with value from another table with duplicates
I am trying to update the column (REPT_IND) from table A to the value in table B where A.ID = B.ID and some conditions in table B. There are some duplicates in table B, but nonetheless the REPT_IND is …
Count times date occurs between two dates in a second table
I have two tables [Charges] and [Defects] and want to produce [Desired Query Output] where the output counts the occurrances of defect when [Charges].ChargeDate is between (and including) [Defects].OpenDate and [Defects].CloseDate. For [Defects] table, a close date of NULL means it has not closed yet. Seems s…
tsql to find members not contacted in past 18 months
I have a table that has ContactNumber (Bigint), MemberNumber(bigInt) and ContactDate(date). The table has millions of records (sample data image attached). I want to get all the records where member has not been contacted for last 18 months. How do I do that using tsql? Your help is highly appreciated. Thanks…
Oracle “NOT IN” not returning correct result?
I’m comparing two tables that share unique values between each other using NOT IN function in Oracle but I’m getting the output is: 521254 for all charging ids –< this is the total unique charging ID’s in BILLINGDB201908 Now I want to find id’s in table BILLINGDB201908 that al…
How to return several columns for subquery added to select?
I have a query like this: select e.field1, e.field2, (select count(field3) from tbl1 where someField = e.field1 group By someType ) as count_1, (select count(field4) from tbl1 where …