query

Linq to Xml, Filter Element from Query (C#)

I'm simply trying to query an xml document and iterate over the results minus specific elements. Ideally I would like to achieve this in the query rather than removing it from the collection before or during iteration. <body> Stuff I want <element> Stuff I dont want </element> </body> I tried something along these l...

Getting a wellformed sql-result with habtm relations

Hi there… I need to write an SQL-Query for a csv-export. I have a table "stores", "items_stores" and therefor "items". Stores HABTM Items. Now i want a result that has a column for the items. Like this: | Name | Lat | Lng | Items | | Petes shop | 123 | 123 | Snacks, Pizza | | Mama Pasta | 123 | 123 | Pasta, Pizza | Yo...

Expression Trees as the basis for user defined query engine (using EAV)?

I'm supporting a multi-tenant system that allows users to define custom forms. The data model is EAV based because issuing dynamic DDL to change table schema doesn't work when supporting multiple tenants in a single DB. An upcoming requirement is to build a flexible query designer that allows users to setup simple predicates against th...

MySql queries: really never use SELECT * ?

I'm a self taught developer and ive always been told not to use SELECT *, but most of my queries require to know all the values of a certain row... what should i use then? should i list ALL of the properties every time? like Select elem1,elem2,elem3,....,elem15 FROM...? thanks ...

On Google App Engine (GAE), how do I search on the Key/ID field?

I've got this code (Java, GAE): // Much earlier: playerKey = KeyFactory.keyToString(somePlayer.key); // Then, later... PersistenceManager pm = assassin.PMF.get().getPersistenceManager(); Key targetKey = KeyFactory.stringToKey(playerKey); Query query = pm.newQuery(Player.class); query.setFilter("__key__ == keyParam"); query.declarePara...

How to create Many-One Mapping in hibernate?

Hi, I want to create Many-One Mapping between two tabels, Expense(ID, NAME, CATEGORY) and Category(ID, NAME). In my class i have created a field 'Category category' and its setters and getters. I did them after seeing some stuff from internet. What are all the changes i have to do in my Category.java class. For now, its looks like, pub...

Invalid Column Name though it's there!!

Hello I'm trying to print out Tables from the DB that have the EntityId column equals to DataclassId column here is the code public void getRootTables_checkSP() { string connect = "Data Source= EUADEVS06\\SS2008;Initial Catalog=TacOps_4_0_0_4_test;integrated security=SSPI; persist security info=False;Trusted_Connection=...

How do I write a subquery in cakephp

Hello everyone, I am totally lost in implementing the following sql using cakephp ORM SELECT * FROM users WHERE users.id NOT IN ( SELECT potentials.user_id FROM potentials ) I have tried this but it is not working at all $this->User->find('all', array( 'contain' => array( 'City' => array( ...

what mysql query is better?

i got 2 mysql queries, that retrieve the same data?, what one do you think is better to use and why? taking into account standards, and better code etc. sorry if this is a stupid question, but im a curious cat! so here goes: QUERY 1: SELECT * FROM (( SELECT u.username, u.picture, m.id, m.user_note, m.reply_id, m.reply_name, m.dt FR...

Help me work out how to build my SQL for this JOIN scenario

I'm still trying to learn SQL as of this writing, so I'm a bit shakey at the moment. My situation is like this: I have a table called 'Tasks' with an auto-incrementing primary key ID and a text field (and a few others that aren't relevant to this problem, too). I have another table called 'Locations' with a foreign key referring to a ...

Getting the maximum value of records

Hello All, I am having a table called Withdrawals which has : Name, year, period, amount. EX: Name Year Period Amount -------------------------------- Jim 2010 1 100 Jim 2009 4 99 Jim 2009 3 17 Kim 2007 2 234 Ryu 2008 5 555 I was stuck that I can't get the...

Add condition to linq query

I'm building a repository layer for nhibernate, in which I have the following find method: public IList<TEntity> Find(Expression<Func<TEntity, bool>> query) I would like to add a condition to the query in that method. The extra condition should limit rows to all where RemovedAt is larger than DateTime.Now. Explanation using SQL: Le...

Conditional Where statement on a table valued parameter?

I'm building a query that has a bunch of optional parameters, some of which are Table-Valued Parameters. The problem that I'm facing is how to most efficiently use the TVPs in this query? Each TVP has the type: TABLE( [variable] nvarchar(30)) I know that I could typically: INNER JOIN @TVP to filter out anything that is not in the ...

EF Query Help. Difficult query with interhited types and self referencing.

I'm still new to EF, and I have figured out some basic queries. I'm stumped on one of the more difficult things I am trying to acheive. I have 4 entities User, Tenant, Building, Apartment Tenant inherits from User At this point, a user who is not a Tenant can be thought of as a Landlord. User (Landlord) has one to many Buildings B...

PHP MySQL Query doesn't work, but works from terminal

Here's my code: $gid = (int) stripslashes($_POST['id']); echo $gid; $db = dbConnect(); $test = $db->query('update games set played = played + 1 where id = "$gid"'); echo $db->error; echo $db->errno; die(); } It works fine from the terminal, and it correctly prints out $gid, and no errors are returned. Am I missing something really obvi...

make differential in DATE query in MySQL

what is the different from this mysql query: WHERE MONTH(date) = MONTH(CURRENT_DATE) and this WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE() i have tried both but i cant see the different. ...

SQL: Need help creating a one-to-many Set comparing query

The parameters are thus: I have a table called Tasks with columns ID (primary key) and label (text). I have another table called Locations with a foreign key referencing Tasks' ID and a name for a location (text). In my code I have a set of locations. UPDATED: I need a query to return all tasks that have associated locations found with...

How can I query how much time a SQL server database restore takes?

Hi Im trying to write a query that will tell me how much time a restore (full or log) has taken on SQL server 2008. I can run this query to find out how much time the backup took: select database_name, [uncompressed_size] = backup_size/1024/1024, [compressed_size] = compressed_backup_size/1024/1024, backup_s...

Convert Binary Id Field to Text

I need the text (representation) of a id field in SQL Server 2005. Is there a way, we can generate the textual representation of the id field? For instance, if the id field reads as 0x00000000000002F0, I need the text value of 0x00000000000002F0 so that I can run SUBSTR operations on the same. Constraints I am not allowed to create ...

How to list all columns of a given sql query

Hello, is there an easy way to get a list of all columns of a SQL query? They are listed in the header of the results window of SSMS but I can't copy them. Thanks in advance. EDIT: sorry, I found it myself after a little googling: http://vidmar.net/weblog/archive/2008/06/05/save-sql-query-results-with-column-names-in-msssms.aspx ...