logo
down
shadow

SQL find parent where children match over multiple rows


SQL find parent where children match over multiple rows

By : gubergenius
Date : January 12 2021, 01:40 AM
Hope that helps if you need only the name you could try checking for the user with count distinct = and tag = card or tag = bike
code :
select  u.name 
from  users 
inner join submission s on u.name = s.user_name
inner join post rp on s.id = rp.submission_id
where rp.tag = 'car' OR rp.tag = 'bike'
group by u.name 
having count(distinct  rp.tag) = 2 
select u.*
from users u 
inner  join  (
  select  u.name 
  from  users 
  inner join submission s on u.name = s.user_name
  inner join post rp on s.id = rp.submission_id
  where rp.tag = 'car' OR rp.tag = 'bike'
  group by u.name 
  having count(distinct  rp.tag) = 2 
) t on t.name  = u.name


Share : facebook icon twitter icon
Fetch parent table rows whose related children ALL match a given criteria?

Fetch parent table rows whose related children ALL match a given criteria?


By : Pravs
Date : March 29 2020, 07:55 AM
SQL find children that have multiple parent records

SQL find children that have multiple parent records


By : tarek
Date : March 29 2020, 07:55 AM
this one helps. I have a situation where we have inserted duplicated data into some tables. , I think you just want count(distinct):
code :
Select c.s_id, c.co_id, Count(distinct a.id)
from c join
     b
     on c.b_id = b.id join
     a 
     on a.id = b.a_id
Group By c.s_id, c.co_id
having count(distinct a.id) > 1;
Find ID of parent where all children exactly match

Find ID of parent where all children exactly match


By : replayer
Date : March 29 2020, 07:55 AM
With these it helps This scenario can be achieve by adding compound condition in the WHERE clause since you're comparing to a pair value. Then you have to count the result based from the conditions set in the WHERE clause as well as the total rows by of the cohort_id.
code :
SELECT  c.cohort_id
FROM    cohort c
        INNER JOIN cohort_member cm
            ON c.cohort_id = cm.cohort_id
        INNER JOIN entity e
            ON cm.entity_id = e.entity_id
WHERE   (e.entity_type_id = 1 AND e.business_key = 'acc1')      -- condition here
         OR (e.entity_type_id = 1 AND e.business_key = 'acc2')
GROUP   BY c.cohort_id
HAVING  COUNT(*) = 2                                            -- number must be the same to the total number of condition
        AND (SELECT COUNT(*) 
             FROM cohort_member cm2 
             WHERE cm2.cohort_id = c.cohort_id) = 2             -- number must be the same to the total number of condition
SELECT  c.cohort_id
FROM    cohort c
        INNER JOIN cohort_member cm
            ON c.cohort_id = cm.cohort_id
        INNER JOIN entity e
            ON cm.entity_id = e.entity_id
        INNER JOIN test_cohort tc
            ON tc.business_key = e.business_key
                AND tc.entity_type_id = e.entity_type_id
GROUP   BY c.cohort_id
HAVING  COUNT(*) = (SELECT COUNT(*) FROM test_cohort)
        AND (SELECT COUNT(*) 
             FROM cohort_member cm2 
             WHERE cm2.cohort_id = c.cohort_id) = (SELECT COUNT(*) FROM test_cohort)
JPQL count Parent Objects on Multiple Children Match in OneToMany Relationship

JPQL count Parent Objects on Multiple Children Match in OneToMany Relationship


By : user3243183
Date : March 29 2020, 07:55 AM
Hope this helps In a JavaEE JPA web application, Feature entity has bidirectional ManyToOne relationship with Patient Entity. I want to write a query to count the number of Patients who have one or more matching criteria features. I use EclipseLink as the Persistence Provider. , For single feature counts you can use this
code :
select count(f.patient) from Feature f where f.variableName=:name and f.variableData:=data
select count(distinct p) from Patient p, Feature f1, Feature f2 
where 
  p.id=f1.patient.id and p.id=f2.patient.id and 
  f1.variableName=:name1 and f1.variableData:=data1 and 
  f2.variableName=:name2 and f2.variableData:=data2
    public class PatientSpecifications {
      public static Specification<Patient> hasVariable(String name, String data) {
        return (root, query, builder) ->  {
                    Subquery<Fearure> subquery = query.subquery(Fearure.class);
                    Root<Fearure> feature = subquery.from(Fearure.class);

                    Predicate predicate1 = builder.equal(feature.get("patient").get("id"), root.get("id"));

                    Predicate predicate2 = builder.equal(feature.get("variableName"), name);
                    Predicate predicate3 = builder.equal(feature.get("variableData"), data);

                    subquery.select(operation).where(predicate1, predicate2, predicate3);

                    return builder.exists(subquery);
        }
      }
    }
@Repository
public interface PatientRepository 
    extends JpaRepository<Patient, Long>, JpaSpecificationExecutor<Patient> {

}
@Service
public class PatientService {    

   @Autowired
   PatientRepository patientRepository;

   //The larger map is, the more subqueries query would involve. Try to avoid large map
   public long countPatiens(Map<String, String> nameDataMap) {
         Specification<Patient> spec = null;

         for(Map.Entry<String, String> entry : nameDataMap.entrySet()) {
            Specification<Patient> tempSpec = PatientSpecifications.hasVariable(entry.getKey(), entry.getValue());
            if(spec != null)
              spec = Specifications.where(spec).and(tempSpec);
            else spec = tempSpec;

         }

         Objects.requireNonNull(spec);

         return patientRepository.count(spec);        
    }
}
Delete rows from parent table after deleting multiple children SQL Server

Delete rows from parent table after deleting multiple children SQL Server


By : yogirana
Date : March 29 2020, 07:55 AM
This might help you I have a table that has foreign key in 4 other tables. I deleted the rows in the children tables and in order to delete from the parent, I do a query where not exist, since I have deleted the references. But I still have an issue on writing the query since it returns an empty result set.
code :
so, I set up two tables... ParentTable with a key of ParentID, 
and ChildTable, key of ChildID and FK of ParentID.


delete dbo.parenttable
where parentid = 3
-- produces error because rows exist in dbo.childtable where parentid = 3

delete dbo.childtable 
where parentid = 3
-- deletes all rows in dbo.childtable where parentid = 3

-- Assuming This is where you are now
--- .... needing to find all rows in parent table 
---      where there are no corresponding child 
---      rows in dbo.childtable


with CTE_Parent as          --wrap up the selected ID's in a CTE expression
(select dbo.parenttable.parentid
from dbo.parenttable
left outer join dbo.childtable 
on dbo.parenttable.parentid = dbo.childtable.parentid   
where dbo.childtable.parentid is null  --- trick to find non-existent child recs
)

delete dbo.parenttable
from dbo.parenttable
inner join cte_Parent
on dbo.parenttable.parentid = cte_parent.parentid
Related Posts Related Posts :
  • Date Condition with Case when Holiday exclude
  • SQL Query Select from 1 table and return data based on 2 columns
  • how to use rank/join and where together
  • How to create new column with values counting up every 9th value with SQL?
  • More Elegant way to Batch Validate Phone Numbers
  • A combo box that has to be filter the same data in that field
  • Exclude updated record from trigger function
  • Insert into table from select only when select returns valid rows
  • How to fix SQLite3::SQLException: near "to": syntax error: SELECT "".* FROM "" WHERE "
  • Filter msdb.dbo.sysjobsteps.command into seperate columns
  • How to extract year or month,day from oracle function without using sub-query?
  • Reset sum when condition is met in Oracle
  • Date Between (Start & Now)
  • Reset running sum when condition is met in Oracle
  • Why is my date criteria filtering incorrectly in a MsysObjects query using DCount?
  • Stripping date/time delimiters in an SSIS csv export
  • SSIS Error on insertion - Error when new primary keys in Dimension table
  • How can I make a temp table of specific datetime data within a range?
  • AnalysisException: Syntax error in SQL After multiple join
  • SQL Query Multiple Columns Using Distinct on One Column Only and Using Order By
  • Sum inventory item groups
  • TSQL: Parsing strings with various characters
  • Using SQL Server : how to use select criteria based on sum
  • Inserting dynamic pivot Result into Temp table
  • Joining multiple fields in a dataset joined with descriptions
  • Conditional WHERE EXISTS in Oracle query
  • How to get the count of each unique value in a column between two dates?
  • SQL: Select only if multiple values are IN 1 column from CatalogTable
  • In a query (no editing of tables) how do I join data without any similarities?
  • Create nested json blobs in PostgreSQL
  • Getting extra dates while using cte
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • Select inner join returns wrong result
  • Group By showing duplicates
  • Error in using subquery in access join query
  • How do I use a composite pk in a stored procedure?
  • How to convert decode function in oracle to standard big query
  • Postgresql: An alternative to subqueries to make the query more efficient?
  • Use returned values from a query in the same procedure
  • Sum all numeric columns in database and log results
  • Release item in current year
  • Conditional function not working as intended postgresql
  • SQL trigger, results not Grouped by
  • getting more records than expected with simple query in postgresql
  • get first occurrence of a certain combination of columns
  • Converting string timestamp to local-time zone in Oracle
  • Redshift - Filter out rows that are blank to left or right of a character
  • Teradata - Know the load volume by times of a table
  • How do I replace duplicate data with random data of same length in SQL
  • How to group by using pivot table
  • How can I achieve a Join for merging two tables FULL OUTER JOIN and avoid duplicates?
  • Multi Keyword search on multi-column sql
  • Computing the median of salaries under each manager in BigQuery SQL
  • How can insert value by selecting from another
  • Retrieving only the latest item for a set of data
  • update column with concatenating for that group only
  • SQL `SELECT 'some string value' AS fieldname` equivalent in Powershell?
  • How to determine the AVERAGE number of associated records
  • shadow
    Privacy Policy - Terms - Contact Us © festivalmusicasacra.org