Tag Info

Hot answers tagged

3

You can rewrite the LEFT JOIN to a subquery, but it may still compile as an outer join query - I have no knowledge of those aspects of the H2 platform. SELECT * FROM tSysNls WHERE nlsGuid IN ( SELECT COALESCE( --- first option: (SELECT de_AT.nlsGuid FROM tSysNLS AS de_AT WHERE de_AT.nlsClazz=x.nlsClazz AND ...


3

You can get the result by using a combination of a WHERE, GROUP BY, and HAVING to return the products that have both the Book and Audio values. select p.id, p.name from products p inner join properties t on p.id = t.product_id where t.key = 'Format' and t.value in ('Book', 'Audio') group by p.id, p.name having count(distinct t.value) = 2; See SQL ...


2

This is tricky question, specially since 5.6, in which the optimizer has changed the way subqueries are executed. Specially, having into account that the definition of "derived table" may change person to person From 4.1 to 5.5, in many cases, either a temporary table would be created or a cross join will be done between tables, filtering later. In some ...


1

The reason the count differs is due to multiple rows being present in tableA that have a foreign key reference to the same row in tableB. When using an inner join the SELECT produces a separate row for each of the records in tableA. The DELETE statement is only affects tableB. As mentioned in ypercube's comment, if you were to do a distinct count, the ...


1

Your question is whether temporary table are stayed in memory or in file-system. Actually it is easy to check in MySQL documents tmp_table_size there are some answers already in DBA STACKEXCHANGE http://dba.stackexchange.com/a/29638/41541


1

There are many ways to do this. Most common are with a LEFT JOIN / IS NULL check, NOT IN or NOT EXISTS subquery. Here's a solution with the 3rd option: SELECT t2.id, t2.name FROM table2 AS t2 WHERE NOT EXISTS ( SELECT * FROM table3 AS t3 WHERE t3.table1_id = @t1_id -- the t1.id value (choice) that you want to check AND ...


1

Solution this is not exactly what i asked for but it solves the problem of the original query to fail on H2 Database thanks ypercube your suggestion is correct SELECT * FROM tSysNls WHERE nlsGuid IN ( SELECT CASE WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid END AS nlsGuid ...



Only top voted, non community-wiki answers of a minimum length are eligible