I want to do a query containing 'like' and 'not like'. Current example: i want everything starting with '1|%' but not with '1|6|199|%' or '1|6|200|%'. Current query: 'SELECT * FROM `links` WHERE `category` LIKE '1|%' NOT LIKE '1|6|199|%','1|6|200|%' ORDER BY `score` DESC LIMIT 9'. But that doesn't work. Any tips? thx ...
The structure of the table "testtable" is id int primary key productid int attributeid int value varchar(250) where productid is the unique id of a product, attributeid is the unique id of attribute of a product e.g. size, quality,height, color and 'value' is the value for the attribute i have to filter a result. I achieve the re...
I've just started with db4o and I stumbled on to a problem. I have an object with a subobject (it is probably not the correct word but I hope you get what I mean). The subobject contains two dates, one start date and one end date. I would like to show the main object if it has at least one sub object where DateTime.Now is inbetween th...
I have a table like this Event ID | Contract ID | Event date | Amount | ---------------------------------------------- 1 | 1 | 2009-01-01 | 100 | 2 | 1 | 2009-01-02 | 20 | 3 | 1 | 2009-01-03 | 50 | 4 | 2 | 2009-01-01 | 80 | 5 | 2 | 2009-...
Given 2 tables called "table1" and "table1_hist" that structurally resemble this: TABLE1 id status date_this_status 1 open 2008-12-12 2 closed 2009-01-01 3 pending 2009-05-05 4 pending 2009-05-06 5 open 2009-06-01 TABLE1_hist id status date_this_status 2 open 2008-12-24 2 pending 2008-12-26 3 open 2009-04-24 4 open...
Our sites are getting pounded pretty hard so we're taking a look into optimizing some of our existing queries. While looking into this we ran across several queries whose execution plan was about 4-5 times faster when a simple reference of the clustered index is in the query... for example If this was the old query: SELECT ... FROM my...
The use case here is to find some restricted set of Boys who have Kites with lengths in a certain range, and then optionally retrieve all of the Kites in that range belonging to those boys. Do not assume that I know much about SQL or Hibernate. I would like to form a Hibernate Criteria query where an associated collection has been res...
I'm trying to offer a feature where I can show pages most viewed by friends. My friends table has 5.7M rows and the views table has 5.3M rows. At the moment I just want to run a query on these two tables and find the 20 most viewed page id's by a person's friend. Here's the query as I have it now: SELECT page_id FROM `views` INNER J...
I'm doing some work for a site on a shared hosting environment that has a history of flipping settings on us. This week, an update script suddenly stopped working, and the underlying reason is that a NOT IN clause that used to return results is no longer doing so. The query, in its essence, is this: SELECT * FROM db1.entry where entr...
tryng to select table rows and then use subqueries to generate single row list (item1,item2,item3) anyway sql: select username, concat(firstname || ' ', lastname) as name, email, phone, ( select ltrim(sys_connect_by_path(res, ', '), ', ') from ( select count(*) over() as cnt, row_number() over(orde...
Let's say I have a function call on a select or where clause in Oracle like this: select a, b, c, dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3) from my_table A similar example can be constructed for MS SQLServer. What's the expected behavior in each case? Is the HASH function going to be called once for each row in the table, o...
Hello! I wonder, how are systems for fulltext search implemented, to be able to query millions of entries very fast? Please note: I'm not talking about systems which tokenize the content by separating it at whitespaces, but about system which are able to query even parts from the middle of tokens (which is a real challange). Background...
I have a table like this: col1 col2 2001 3001 2002 3001 2003 3001 2004 3002 2002 3003 2001 3003 i want to create it a query that finds out all the combination of items in col1 with a certain item in col1 if they occur to the same item in col2 and the frequency of occurrence. For example, if we want to explore item numbe...
I'm trying to make a WordPress site that has six lists on a page, each list showing posts from a different category. Simple. But then, if a user selects a tag, taking them to that tag archive page, I want them to still see the six-list template, but all the posts within each category are also filtered by the tag. So lists of posts are f...
I would like to form a query where an associated collection has been restricted, ideally with Hibernate Criteria or HQL, but I'd be interested in how to do this in SQL. For example, say I have a Boy class with a bidirectional one-to-many association to the Kites class. I want to get a List of the Boys whose kites' lengths are in a range...
Consider the following table structure with data - AdjusterID | CompanyID | FirstName | LastName | EmailID ============================================================ 1001 | Sterling | Jane | Stewart | [email protected] 1002 | Sterling | David | Boon | [email protected] 1003 | PHH ...
Hi In our product we have a generic search engine, and trying to optimze the search performance. A lot of the tables used in the queries allow null values. Should we redesign our table to disallow null values for optimization or not? Our product runs on both Oracle and MS SQL Server. ...
User has pets, pets may have pictures One picture may be set as mainpicture (mainpic=0 or mainpic=1) I need to get all the pet data from the pets table and I also need the picture id where mainpic=1 or first picture or null if he has no picture in the picture table. I need to get the pet data even if it has no picture at all. Currently...
I am trying to call a query from Delphi 6 with bind variables, using the DOA component set's TOracleQuery object. I have 6 variable, 1 of which is a string (VarChar2 in Oracle lingo). I have tried calling query.DeclareVariable with the otVarchar2 enumeration, which intuitively makes sense, the otString enumeration (as the documentation r...
Hi, We have this weird issue regarding SQL Query Notification service. We have a main web application running with QN service so the cache item in the web get notification from SQL when the monitoring data changed. And it runs well. What is weird is when the other supporting web service tries to register QN subscription into SQL...