query

TSQL Finding Incomplete courses

From the StudentDetails Table StudentDetails SID Name CourseCompleted 1 Andrew CS001 1 Andrew CS002 1 Andrew CS003 2 Grey CS001 2 Grey CS005 2 Grey CS002 3 Jon CS002 3 Jon CS005 3 Jon CS008 How to generate the folowing output ( Course not completed by each stud...

JPQL LIKE expression on enum

Can JPQL execute LIKE expressions against enums? If I have an entity Foo with an enum field bar I can execute the following in MySQL(bar is stored as a MySQL enum)... SELECT * FROM Foo WHERE `bar` LIKE '%SUFFIX' However, the corresponding query in JPQL... SELECT f FROM Foo f WHERE f.bar LIKE '%SUFFIX' ...complains that... Param...

Running Counter in SQL 2008

Hi all, I'm trying to establish a temp table for a running inventory demand report in SQL 2008. I can get the results that I am looking for until I hit an invoice with multiple lines. Here is the code so far: --Gather Current Order Data DECLARE @b TABLE ([Planned Shipment Date] DATE, [Order] VARCHAR(15), [Line Number] VARCHAR(15), [L...

Problem with case and status

When I don't have right side I get NULL on column StatusOfDeduplication instead 5. What is wrong with this query ? select c.Code AS Code, c.DefaultName AS Name, c.Status AS Status, case cp.TargetCodeStatus when Null then 5 else cp.TargetCodeStatus end as StatusOfDeduplication from Cities c LEFT JOIN CityPackages cp ON cp.TargetCode ...

mysql query optimization

SELECT b.categoryid, SUM(viewcount) AS cnt, categoryname FROM bookvisit AS bv INNER JOIN book AS b ON b.isbn = bv.isbn LEFT JOIN category AS c ON b.categoryid = c.categoryid WHERE b.categoryid IS NOT NULL AND b.categoryid <> 0 GROUP BY b.categoryid ORDER BY cnt DESC, bv.isbn LIMIT 0, 4 I ha...

NoSql for saving SOAP requests to DB for audit logging - and then querying?

Currently we are saving all SOAP requests to a log file (rolling, one per day). When I get a request about who did what, when, how often etc - then I need to resort to heavy duty text manipulation in order to extract that information. My idea was to save those SOAP requests to a database where I could perform some queries. A SOAP requ...

I'm not getting the expected result from an SQL query.

I'm developing a search function for a website. I have a table called keywords with two fields id and keyword. I have two separate search queries for AND and OR. The problem is with the AND query. It is not returning the result that I expect. The printed SQL is : SELECT COUNT(DISTINCT tg_id) FROM tg_keywords WHERE tg_keyword='keywor...

SQL DSL for Scala

I am struggling to create a SQL DSL for Scala. The DSL is an extension to Querydsl, which is a popular Query abstraction layer for Java. I am struggling now with really simple expressions like the following user.firstName == "Bob" || user.firstName == "Ann" As Querydsl supports already an expression model which can be used here I dec...

Howto build Subsonic 3 qeury with dynamic table and column names using reflection

I would like to construct a dynamic Subsonic 3 query using code to get a collection of a type. The SQL would like this: select * from @tableName where @columnName1 = @columnValue1 The subsonic query would look like this: List<object> = new DB.Select.From<getTypeClass(tableName)>.Where(columnName1).IsEqualTo(columnValue1).ExecuteTypeL...

Lucene Boolean value search with boolean query

Hi, There is a field say XYZ with value as either TRUE or FALSE. i am searching as following +Contents:risk +XYZ:TRUE is it legal to search like that? i tried but it showed me results with FALSE value too. What was more amazing is that i searched by +XYZ:[TRUE TO TRUE] and it worked. can some one tell me what exactly is my mistak...

sql search query still not producing expected results.

I need a count and search query to work properly. The count query appears to be working properly, however the search query is not. Count query: SELECT COUNT(DISTINCT tg_id) FROM tg_keywords WHERE tg_keyword LIKE 'keyword_1' OR tg_keyword LIKE 'keyword_2' ORDER BY tg_keyword LIKE 'keyword_1' AND tg_keyword LIKE 'keyword_2 DESC Re...

Querying remote system via WMI returns access denied

Hi all, I have a simple WMI query that runs fine locally, but querying a remote system gives an access denied error. When I add the local user to the remote system as member of the administrator group, the query works as expected, but I don't want to have that user as a member of that group. I already tried the following things, unfort...

sql query to find sum of all rows and count of duplicates

If data is in the following format: SID TID Tdatetime QID QTotal ---------------------------------------- 100 1 01/12/97 9:00AM 66 110 100 1 01/12/97 9:00AM 66 110 100 1 01/12/97 10:00AM 67 110 100 2 01/19/97 9:00AM 66 . 100 2 01/19/97 9:00AM 66 110 100 2 01/19/97 10:00A...

nhibernate fetching entire tree

Hi, I need help fetching tree hierarchy from db using nhibernate 3.0 QueryOver.Of<Enterprise>(() => entAlias) .JoinAlias(() => entAlias.ChildEntities, () => childEntityAllias, JoinType.LeftOuterJoin) .TransformUsing(new DistinctRootEntityResultTransformer()) I am getting only the two level of the graph (parent and its child...

MySQL SUM Query

Greetings, I've got a query that I'm struggling with, this is the first time that I am encountering this type of query. I have two table as shown below. xid is the primary key in parent_tbl1, while xid is the foreign key in child_tbl2 parent_tbl1 xid pub 1 1 2 1 3 0 4 1 child_tbl2 id ttype fno xid qnty 1 ...

MySQL: Cannot create table with VARBINARY?

I am running this query to set up a VARBINARY (I wish for it to be so, for a real reason) field for my database: CREATE TABLE `test_books` (`id` int UNSIGNED NOT NULL,`book` VARBINARY, `timestamp` int(11) NOT NULL, UNIQUE KEY `id` (`id`)) It hands me a standard syntax error telling me to check all the remaining code after 'VARBINARY'...

Querying Android db for specific piece of data

I'm creating a method that will see if a column in a database row is populated; and if so, it returns the varchar in that row, else it calls a web service to return that data. My question is: What is the standard way of simply selecting a column with Android? (select mycolumn from mytable where _id = x;) I've created a ContentProvider...

Problem with an agenda/availability query

I have a mysql table with users and their weekly calendar. Every user can set his own availability for the week (morning, afternoon, night / MON thru SAT), and that is not going to change often, almost never. Imagine those users are personal trainers in a gym, or tennis courts you can book... My problem here is to find the right quer...

Escaping reserved words

Sitecore provides a way of escaping words within a Sitecore query that contain characters that they don't like. Such characters include hyphens and spaces. In the interest of simplifying my life, I wrote a simple helper function that would escape every part of a Sitecore query, and it worked fine for a while: public static string Escape...

TSQL Understanding the execution of the Query

I have two tables Table Visitor ID Name CityName 1 Jon NY 1 Jon KY 2 Paul NY 1 paul TY Table City ID CityName 1 NY 2 KY 3 TY I have to list the visitor who has visited all cities in City Table. I took the query from web,but i do not the how it works internally. The query is select distin...