New answers tagged subquery
There are so many ways to achieve this. here is one SELECT *, CASE WHEN (col1 = "this" AND col2 != "that") THEN TRUE ELSE FALSE END AS notavail FROM `myTable` HAVING notavail = 0 ; here is another SELECT *, IF(col1 = "this",col2 != "that",0) AS notavail FROM `myTable` HAVING notavail = 0 ; here is another SELECT * FROM `myTable` ...
How about SELECT * FROM myTable WHERE NOT (ColX = 'this' and ColY <> 'that')
Select * from mytable where colx='this' and coly='that';
Like this: select * from myTable where colY = 'that' or colX <> 'this' The standard implementation of any criteria specified as A => B is as B or not A Update: Using this CTE for sample data: with myTable as ( select N,colX,colY from ( values (1,'this','hey') ,(2,'this','that') ,(3,'yup', 'yerp') ...
There are two ways (not mutually exclusive) to improve query performance. First, you could re-write the correlated subquery into a subquery with an aggregate: SELECT m.memb_id, m.firstname, m.lastname, t.LastLogin FROM menmembers AS m LEFT JOIN ( SELECT cod_usr, MAX(datetr) AS LastLogin FROM trank GROUP BY cod_usr ) AS t ...
Filters after the GROUP BY is executed can be applied with the HAVING clause, which is equivalent to a WHERE condition, but for the latest stage of the execution. So, assuming the rest of the query is right: SELECT u.uid AS uid, u.name AS name, u.picture AS picture, n.field_full_name_value AS field_full_name_value, f.field_function_value AS ...
Your query is slow because of the sub-select. It is what is known as a correlated sub query. It will be executed once per row in the outer query. This question answers a similar problem. It's looking for the minimum, you for the maximum; it's using one table, you're using one. The translation shouldn't be too hard, though.
Identical subqueries are normally only performed once. However, the way you write a query isn't neccessarily the actual execution order, so there are no guarantees. To make sure, view your estimated execution plan with one or both of the subqueries, and compare them. In your example, you could place the subquery in a JOIN instead, which would make it both ...
The elegance of the answer will vary with the DBMS of your choice. In it's simplest form: select name, city as address from temp1 union select name, phone_no as address from temp1 union select name, pincode as address from temp1 I believe this should be supported by most DBMSes. If your DBMS supports lateral (cross apply in sqlserver?) you can do ...
Top 50 recent answers are included