Join two tables where all child records of first table match all child records of second table
By : mattcfish
Date : March 29 2020, 07:55 AM
This might help you Updated! to Felix for pointing out a flaw in my existing solution (3 years after I originally posted it, hehe). After looking at it again, I think this might be correct. Here I'm getting (1) the customers and limits with matching categories, plus the number of matching categories, (2) the number of categories per customer, (3) the number of categories per limit, (4) I then ensure the number of categories for customer and limits is the same as the number of the matches between the customers and limits: code :
select
matches.name,
matches.limit
from (
select
c.name,
c.customerId,
l.limit,
l.limitId,
count(*) over(partition by cc.customerId, lc.limitId) as matchCount
from tblCustomer c
join tblCustomerCategory cc on c.customerId = cc.customerId
join tblLimitCategory lc on cc.categoryId = lc.categoryId
join tblLimit l on lc.limitId = l.limitId
) as matches
join (
select
cc.customerId,
count(*) as categoryCount
from tblCustomerCategory cc
group by cc.customerId
) as customerCategories
on matches.customerId = customerCategories.customerId
join (
select
lc.limitId,
count(*) as categoryCount
from tblLimitCategory lc
group by lc.limitId
) as limitCategories
on matches.limitId = limitCategories.limitId
where matches.matchCount = customerCategories.categoryCount
and matches.matchCount = limitCategories.categoryCount
|
SQL Query to find Parent records without child records.Both child and parent records are on the same table
By : Thomas Appleby
Date : March 29 2020, 07:55 AM
hop of those help? A SELF JOIN on the table as follows would give the parents without children. code :
-- relations (record_id, parent_id)
SELECT parents.record_id
FROM
relations parents
LEFT JOIN relations children
ON parents.record_id = children.parent_id
WHERE
children.record_id IS NULL
|
How to delete existing records and insert new records on a mapping/child table in Hibernate/JPA
By : Mr. Effting
Date : March 29 2020, 07:55 AM
wish helps you I assume that the UserProject mapping is a consciuse choice because of the roles additional data (if not you normally do not map that table). The orphan removal obviously cannot work as it is referenced by both User and Project table. code :
public User updateUserDetails(User template){
User toUpdate = EM.find(User.class,template.getId());
Set<UserProject> present = template.getUserProjects();//the ones should be kept
Set<UserProject> toDel = new HashSet<>(toUpdate.getUserProjects());
toDel.removeAll(present); //the difference so the once should be removed
Set<UserProjcet> newOnes = new HashSet<>(present);
newOnes.removeAll(toUpdate.getUserProjects()); //freshly added
for (UserProject uP : toDel) {
up.getProject().getUserProjects().remove(uP); //from each project removing reference to UserProject
toUpdate.getUserProjects().remove(uP);
EM.remove(uP); //removing the actuall
}
toUpdate.getUserProjects().addAll(newOnes);
return toUpdate;
}
|
How to insert the records in child table when records are inserted into parents table in SSIS?
By : user5652473
Date : March 29 2020, 07:55 AM
around this issue From your description I take for granted, that you know WHICH child records have to be cretaed for WHICH master record, so I guess there are basiacally two possibilities. All data is known at runtime: Create a dataflow, which populates the master table Cretae a second dataflow, which populates the child table and connect it to the first dataflow via Succeed constraint
|
Loading more records than actual in HIve
By : spears
Date : March 29 2020, 07:55 AM
should help you out If hive.compute.query.using.stats=true; then optimizer is using statistics for query calculation instead of querying table data. This is much faster because metastore is a fast database like MySQL and does not require map-reduce. But statistics can be not fresh (stale) if the table was loaded not using INSERT OVERWRITE or configuration parameter hive.stats.autogather responsible for statistics auto gathering was set to false. Also statistics will be not fresh after loading files or after using third-party tools. It's because files was never analyzed, statistics in metastore is not fresh, if you have put new files, nobody knows about how the data was changed. Also after sqoop loading, etc. So, it's a good practice to gather statistics for table or partition after loading using 'ANALYZE TABLE ... COMPUTE STATISTICS'. In case it's impossible to gather statistics automatically (works for INSERT OVERWRITE) or by running ANALYZE statement then better to switch off hive.compute.query.using.stats parameter. Hive will query data instead of using statistics.
|