LIKE without wildcard character is equivalent to =. Assuming you actually meant name=”text”. Indexes are the key to performance. Test setup CREATE TABLE image ( image_id serial PRIMARY KEY , group_id int NOT NULL , name text NOT NULL ); Ideally, you create two indexes (in addition to the primary key): CREATE INDEX image_name_grp_idx ON … Read more
CodeIgniter’s ActiveRecord doesn’t support UNION, so you would just write your query and use the ActiveRecord’s query method. $this->db->query(‘SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2’);
Here’s a quick little module I wrote that allows you to UNION multiple scopes. It also returns the results as an instance of ActiveRecord::Relation. module ActiveRecord::UnionScope def self.included(base) base.send :extend, ClassMethods end module ClassMethods def union_scope(*scopes) id_column = “#{table_name}.id” sub_query = scopes.map { |s| s.select(id_column).to_sql }.join(” UNION “) where “#{id_column} IN (#{sub_query})” end end end … Read more
There are a number of ways to do this, depending on what you really want. With no common columns, you need to decide whether you want to introduce a common column or get the product. Let’s say you have the two tables: parts: custs: +—-+———-+ +—–+——+ | id | desc | | id | name … Read more
You could use id in (select id from …) or id in (select id from …) e.g. instead of non-working from Person p where p.name=”Joe” union from Person p join p.children c where c.name=”Joe” you could do from Person p where p.id in (select p1.id from Person p1 where p1.name=”Joe”) or p.id in (select p2.id … Read more
This is a very good question. I implemented the same algorithm on c# some time ago. The Algorithm constructs a common contour of two polygons (i.e. Constructs a union without holes). Here it is. Step 1. Create graph that describes the polygons. Input: first polygon (n points), second polygon (m points). Output: graph. Vertex – … Read more
Sometimes you need to have the ORDER BY in each of the sections that need to be combined with UNION. In this case SELECT * FROM ( SELECT table1.field1 FROM table1 ORDER BY table1.field1 ) DUMMY_ALIAS1 UNION ALL SELECT * FROM ( SELECT table2.field1 FROM table2 ORDER BY table2.field1 ) DUMMY_ALIAS2
You can do this by adding a pseudo-column named rank to each select, that you can sort by first, before sorting by your other criteria, e.g.: select * from ( select 1 as Rank, id, add_date from Table union all select 2 as Rank, id, add_date from Table where distance < 5 union all select … Read more
Just write Select id,name,age From Student Where age < 15 Union Select id,name,age From Student Where Name like “%a%” Order by name the order by is applied to the complete resultset
Quoting the docs, To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT: (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);