query

LINQ to XML query

Here I had very similar xml structure, but now I have this: <Fields> <Company>My Company</Company> <Address2>Villa at beach</Address2> <Email2>[email protected]</Email2> <Mobile>333-888</Mobile> <ContactMethod>Facebook</ContactMethod> ...etc... </Fields> And now I need the same output as on the given link: Compan...

How do I test the speed of a mySQL query?

I have a select and query like below... $sql = "SELECT * FROM notifications WHERE to_id='".$userid."' AND (alert_read != '1' OR user_read != '1') ORDER BY alert_time DESC"; $result = mysql_query($sql); how do I test how long the query took to run? ...

MySQL Query: How to select rows that don't have a certain value?

I am having trouble writing a query and I don't even know if it is possible. Take this table for example: id group active 1 A NO 2 A YES 3 A NO 4 B YES 5 B NO 6 C NO 7 C NO Table above is just an example. In real table there are much more columns the those t...

How to select 'previous' and 'next' records in SQL?

I am building a blog-post detail page on my site that will display display a 'previous' and 'next' link similar to how a typepad blog post works. See example below. I am wondering if there is a way to query the database which accomplishes something like the image below where it selects the 'current' record (in yellow) for display, but ...

SQL to get friends AND friends of friends of a user

My MySQL tables structure is like this. USER int id varchar username FRIEND_LIST int user_id int friend_id For each friend relationship I insert 2 records in FRIEND_LIST. If user 1 is friend of user 2 then the next rows are inserted into FRIEND_LIST 1,2 2,1 I want to get the friends and friends of friends of an specific user. The...

Can this sql query be simplified?

I have the following tables: Person, {"Id", "Name", "LastName"} Sports, {"Id" "Name", "Type"} SportsPerPerson, {"Id", "PersonId", "SportsId"} For my query I want to get all the Persons that excersise a specific Sport whereas I only have the Sports "Name" attribute at my disposal. To retrieve the correct rows I've figured out the followi...

How to perform a Linq2Sql query on the following dataset

I have the following tables: Person(Id, FirstName, LastName) { (1, "John", "Doe"), (2, "Peter", "Svendson") (3, "Ola", "Hansen") (4, "Mary", "Pettersen") } Sports(Id, Name) { (1, "Tennis") (2, "Soccer") (3, "Hockey") } SportsPerPerson(Id, PersonId, SportsId) { (1, 1, 1) (2, 1, 3) (3, 2, 2) (...

Find out which row caused the error

I have a big fat query that's written dynamically to integrate some data. Basically what it does is query some tables, join some other ones, treat some data, and then insert it into a final table. The problem is that there's too much data, and we can't really trust the sources, because there could be some errored or inconsistent data. ...

PHP | SQL syntax error when inserting array

Hi guys, I am having some trouble inserting an array into the sql database. my error is as follows: Unable to add : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '06:45:23,i want to leave a comment)' at line 1 My query var_dump is: string(136) ...

TFS 2008, Conchango Scrum For Team Systems, and Work Item Queries

We are using the first version of Scrum for team system in TFS 2008. What I'm trying to do is write a query to get all sprint backlog items in the current active sprint. I'd like to use the sprint start and end dates around the @Today param to get the list. something like this: Team Project = @Project AND Sprint Start <= @Today AND S...

In JPA, does rollback occur for the native queries?

I'm using JPA 1.0 with hibernate as my provider. Inside an entitymanager transaction, if a series of native queries are run (which include DELETE sql statements) and an error occurs, will the native queries rollback too on the error? I ran into a deadlock problem in an oracle database and I noticed that it left the database inconsistent...

How to select distinct field values using Solr?

I would like to do the equivalent of this SQL but with Solr as my data store. SELECT DISTINCT txt FROM my_table; What syntax would force Solr to only give me distinct values? http://localhost:8983/solr/select?q=txt:?????&amp;fl=txt EDIT: So faceted searching seems to fit, but as I investigated it, I realized I had only detail...

How to handle when SSRS does not automatically update fields based on database query?

So I am trying to change the number of fields in my dataset in SSRS and the refresh button is not picking up the added field from the SQL server. The query is definitely returning the correct data, as I have double checked in the server engine itself. Also, I have tried manually adding the field using the SSRS menu, but as soon as I exec...

Getting the most recent post based on date

Hi guys, How do I go about displaying the most recent post when I have two tables, both containing a column called creation_date This would be simple if all I had to do was get the most recent post based on posts created_on value however if a post contains replies I need to factor this into the equation. If a post has a more recent repl...

Help with SQL query

Hello, I have list of DateTime values, and for each value I need to fetch something from the database. I would like to do this with one query. I know it's possible to pass a table (list) to the stored procedure, but Im not sure how to write the query itself. Let's say I have the following table: CREATE TABLE Shows( ShowId [int] N...

MySQL: How can fetch SUM() of all fields in one Query?

Hi, I just want somthing like this: select SUM(*) from `mytable` group by `year` any suggestion? (I am using Zend Framework; if you have a suggestion using ZF rather than pure query would be great!) Update: I have a mass of columns in table and i do not want to write their name down one by one. No Idea?? ...

How to sort a table with some fields positioned according to my conditions

I want to sort a table in sql server. Condition that i need to fulfill is this I have a table that has some records in it like this Select One None Child Old Neutral .. .. .. i want it to be sorted in such a way that Select One comes up and None comes at the end and remaining gets sorted alphabetically. Select One Child Neutral Old ....

Query problems with Hibernate (JPA) and PostgreSQL

I'm trying to use PostgreSQL as the database for Hibernate/JPA. However, I get an error when executing a simple query. My query is as follows: SELECT DISTINCT p FROM UserProfile p ORDER BY :order When I execute the query, I'll pass in a value like "lastLoginDate" for :order. However, I get the following exception when trying to exe...

sql query for 3 tables

i have 3 tables (A,B,C) Table A - ID Name 1 Sam 2 Manuel 3 Jane Table B ID Tab_A_ID Name 1 1 Meer 2 1 Kutti 3 2 Mikaro Table C ID Tab_B_ID Price 1 1 255.11 2 1 30.52 3 3 125.22 I need a query that shall pick up the top price for TableA-Name from TableC. So only 1 top price for 1...

mysql to get depth of record, count parent and ancestor records

Hey All, Say I have a post table containing the fields post_id and parent_post_id. I want to return every record in the post table with a count of the "depth" of the post. By depth, I mean, how many parent and ancestor records exist. Take this data for example... post_id parent_post_id ------- -------------- 1 null 2 ...