For others that find this question looking to delete while using a subquery, I leave you this example for outsmarting MySQL (even if some people seem to think it cannot be done): DELETE e.* FROM tableE e WHERE id IN (SELECT id FROM tableE WHERE arg = 1 AND foo = ‘bar’); will give you … Read more
So you want to get the row with the highest OrderField per group? I’d do it this way: SELECT t1.* FROM `Table` AS t1 LEFT OUTER JOIN `Table` AS t2 ON t1.GroupId = t2.GroupId AND t1.OrderField < t2.OrderField WHERE t2.GroupId IS NULL ORDER BY t1.OrderField; // not needed! (note by Tomas) (EDIT by Tomas: If … Read more
Consider this code: Products::whereIn(‘id’, function($query){ $query->select(‘paper_type_id’) ->from(with(new ProductCategory)->getTable()) ->whereIn(‘category_id’, [‘223′, ’15’]) ->where(‘active’, 1); })->get();
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
Every derived table (AKA sub-query) must indeed have an alias. I.e. each query in brackets must be given an alias (AS whatever), which can the be used to refer to it in the rest of the outer query. SELECT ID FROM ( SELECT ID, msisdn FROM ( SELECT * FROM TT2 ) AS T ) … Read more
What is a LATERAL join? The feature was introduced with PostgreSQL 9.3. The manual: Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.) Table functions appearing … Read more
Sub-queries are the logically correct way to solve problems of the form, “Get facts from A, conditional on facts from B”. In such instances, it makes more logical sense to stick B in a sub-query than to do a join. It is also safer, in a practical sense, since you don’t have to be cautious … Read more
A really easy way to do this is to add a UNIQUE index on the 3 columns. When you write the ALTER statement, include the IGNORE keyword. Like so: ALTER IGNORE TABLE jobs ADD UNIQUE INDEX idx_name (site_id, title, company); This will drop all the duplicate rows. As an added benefit, future INSERTs that are … Read more
Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP’s exact query, please see other answers to this question In MySQL, you can’t modify the same table which you use in the SELECT part. This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html Maybe you can just join … Read more
The subqueries you are using in your select statement should return single value for your query to run without error. The following query will run without any issue, but it won’t give you the result you are expecting. SELECT TOP 10 (SELECT g_name FROM vgsales$ x WHERE g_platform = ‘X360’ AND a.g_rank = x.g_rank) AS … Read more