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();
In the sub-query vs simple (non-recursive) CTE versions, they are probably very similar. You would have to use the profiler and actual execution plan to spot any differences, and that would be specific to your setup (so we can’t tell you the answer in full). In general; A CTE can be used recursively; a sub-query … 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
SQL is a declarative language, not a procedural language. That is, you construct a SQL statement to describe the results that you want. You are not telling the SQL engine how to do the work. As a general rule, it is a good idea to let the SQL engine and SQL optimizer find the best … 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
Postgres allows: UPDATE dummy SET customer=subquery.customer, address=subquery.address, partn=subquery.partn FROM (SELECT address_id, customer, address, partn FROM /* big hairy SQL */ …) AS subquery WHERE dummy.address_id=subquery.address_id; This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.
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
You need to alias the subquery. SELECT name FROM (SELECT name FROM agentinformation) a or to be more explicit SELECT a.name FROM (SELECT name FROM agentinformation) a
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
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