I think answer 4 is correct. There are a few considerations: type of subquery – is it corrrelated, or not. Consider: SELECT * FROM t1 WHERE id IN ( SELECT id FROM t2 ) Here, the subquery is not correlated to the outer query. If the number of values in t2.id is small in comparison … Read more
Below is the pseudo-code for using sub-query using Criteria API. CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery(); Root<EMPLOYEE> from = criteriaQuery.from(EMPLOYEE.class); Path<Object> path = from.get(“compare_field”); // field to map with sub-query from.fetch(“name”); from.fetch(“id”); CriteriaQuery<Object> select = criteriaQuery.select(from); Subquery<PROJECT> subquery = criteriaQuery.subquery(PROJECT.class); Root fromProject = subquery.from(PROJECT.class); subquery.select(fromProject.get(“requiredColumnName”)); // field to map with main-query subquery.where(criteriaBuilder.and(criteriaBuilder.equal(“name”,name_value),criteriaBuilder.equal(“id”,id_value))); select.where(criteriaBuilder.in(path).value(subquery)); … Read more
I have manage to achieve such dynamic search criterion by using Criteria API’s. Problem I ran into was duplicates with inner and outer joins and especially related to sorting and pagination, and I had to resort to using 2 queries, 1st query for restriction and using the result of 1st query as ‘in’ clause in … Read more
You can use a correlated subquery with a WHERE NOT EXISTS clause to ensure that your update will not generate duplicates, like : UPDATE mytable t SET (col1, col2) = (‘AAA’, ‘BBB’) WHERE t.col1 = ‘AAB’ and t.col2 = ‘BBA’ AND NOT EXISTS ( SELECT 1 FROM mytable WHERE col1 = ‘AAA’ AND col2 = … Read more
Use JOIN to join the subqueries and use ON to say where the rows from each subquery must match: SELECT T1.col_a, T1.col_b, T2.col_c FROM (SELECT col_a, col_b, …etc…) AS T1 JOIN (SELECT col_a, col_c, …etc…) AS T2 ON T1.col_a = T2.col_a If there are some values of col_a that are in T1 but not in … Read more
This solution uses the uniqueness of the ContractId field: SELECT MemberID, ContractID, StartDate, EndDate FROM member_contracts WHERE ContractId IN ( SELECT MAX(ContractId) FROM member_contracts GROUP BY MemberId ) See it working online: sqlfiddle
You can user OUTER APPLY SELECT * FROM tbl1 OUTER APPLY ( SELECT TOP 1 currency_id, SUM(taxrate) AS taxrate FROM tbl2 WHERE wuptr.currency_id = tbl1.currency_id GROUP BY tbl2.currencyid )
Subqueries are usually fine unless they are dependent subqueries (also known as correlated subqueries). If you are only using independent subqueries and they are using appropriate indexes then they should run quickly. If you have a dependent subquery you might run into performance problems because a dependent subquery typically needs to be run once for … Read more
by using the GROUP_CONCAT() function and a GROUP BY call. here’s an example query SELECT p.package_id, p.package_name, p.price, GROUP_CONCAT(pz.zone_id SEPARATOR ‘,’) as zone_list FROM package p LEFT JOIN package_zone pz ON p.package_id = pz.package_id GROUP BY p.package_id you should still be able to order by zone_id s (or zone_list), and instead of using LIKE, you … Read more
Just define your CTE on top and access it in the subquery? WITH YourCTE(blubb) AS ( SELECT ‘Blubb’ ) SELECT id, (SELECT blubb FROM YourCTE), name FROM table_name