query

Update with Subquery

Hello there, i have a Problem with a query. I have a huge Table with Zipcodes from Germany called 'Postleitzahlen'. There is another table with Companies called 'Firmen' Structure is like this: Firmen ------ ID City State ZipCode Postleitzahlen -------------- ZipCode State Now I want, all empty ( '' or NULL ) State-fields of Firm...

Need help with a SQL join: Conditional Matching?

I'm joining 2 tables. ACCOUNTS account_number region product 1234 100 AB 2345 0 AB 3456 300 CD 4567 0 CD PRODUCTS product region price AB 100 $1.50 AB 0 $1.25 ...

SQL Server sproc query optimization

I've got an application that generates data for reports that look like: age < 30 | age >=30 | asian | hispanic ----------------------------------------------------------------- clients in prog A | | | ----------------------------------------------------------------- clients in p...

How do I query a database field but ignore the HTML markup?

We have a field that contains HTML markup for formatting on the website, but we need to query just the text that should render on screen, not things like CSS tags, tag names, property names, etc. Is there a way to ignore the markup right in the SQL query or Stored Procedure? If there are ways to do this, will we have performance issues ...

What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

It seems to me that you can do the same thing in a SQL query using either NOT EXISTS, NOT IN, or LEFT JOIN WHERE IS NULL. For example: SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2) SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a) SELECT a FROM table1 LEFT JOIN table2 ON table1.a = ta...

Rails: With models A has_many B belongs_to C return A where B belongs to C and B is most recently created for given A?

I have three models A, B, and C, where A has_many B; B belongs to C, and B has a created_on timestamp. C has_many A through B. I want to return all A where A has a B that belongs to C and where B is the most recently created B for that A. I can do this with a method loop. Can this be done solely with named_scopes?, or is some other ...

sql query sub select on itself confusion

I'm trying to write a sql statement which returns bad rows from my table. I have a table with rows: Key | Date | Indicator 1 | 10/10/08 | SE 1 | 10/11/09 | CB 1 | 10/12/09 | CE 1 | 10/13/09 | TR 2 | 1/1/09 | SE 3 | 10/10/08 | SE 3 | 10/13/09 | CE 3 | 10/15/09 | SL So what I want returned would be all rows where a key has an in...

MySQL sort by average of two averages

I am working on a contest site where there are two types of users, normal site members, and judges. Each can use a drag and drop tool to order the entries in a particular contest in the order they choose. When they are done the relevant entry ids are attached a ranking value that can then be used to determine which entry in contest got t...

Django MySql Raw Query Error - Parameter index out of range

This view is running fine on plain pyton/Django/mysql on Windows I'm porting this to run over jython/Django/mysql and it gives error - Exception received is : error setting index [10] [SQLCode: 0] Parameter index out of range (10 > number of parameters, which is 0). [SQLCode: 0], [SQLState: S1009] The Query is - cursor.execute(...

How to remove all parameters' values from query

I have a number of query strings looks like View.aspx?type=a&boo=bar&i=1 How to remove all parameters' values so it would become View.aspx?type=&boo=&i= For each string set of parameters there is it's own combination of parameters, 2-3 in number. Edit: How to remove all parameters except specific set? ...

Microsoft Access Data Base .. Select Query

i am doing queries practice in Microsoft access. i want to concatenate the first name with the fax number.. the fax number is like (123)555-0103 .. i`m doing that select [first name] +' ''s Fax Number is' +str(fax number) as [Mariya`s Fax Number] from employees where id =4; but it is giving error.. ...

Displaying Query data in a TextInput Field in Flex?

I'm trying to display query data into multiple TextInput Fields in Flex. <mx:TextInput id="stagInput" text="{acContacts}" width="170" x="120" y="74"/> This is what I'm trying but all that displays is [object Object] I think I need to define the database field I'm wanting to display, but I'm unsure how to do this as TextInput fields d...

SQL TOP 10 Ranking Query Help Required

Hi Everyone I have the code below and I am trying to find out the top 10 servers used in the last month, but having issues, not very good with SQL. Need some help or advice if possible. I got this working on top 10 Apps but cannot figure out how to make this happen for top 10 servers. SELECT TOP 10 dbo_LU_SERVERNAME.SERVERNAME, Count(...

Problem when using form-reference in transform/pivot query

Hi! When the query is like this there is no problem This works: TRANSFORM Count(Aktivitet.ID) AS AntalförID SELECT Aktivitet.region, Sum(Aktivitet.antalMän) AS [Antal Män], Sum(Aktivitet.antalKvinnor) AS [Antal Kvinnor] FROM Aktivitet GROUP BY Aktivitet.region PIVOT Aktivitet.aktivitetstyp But when I add this line I get into trouble...

Generate Reports from Ms Access 2007 Using Queries automatically via VBA

Hi Everyone, I have a few queries which need to be exported into a report and I want this to be done automatically each time the database is opened. I'm guessing this would be possible using VBA to which i dont have much knowledge off. Can anyone help with this please? Thank You ...

Sql Server query with date filter

I have a table like this; ID int, OrderedDate DateTime I want to select only records of followed month. For example result set: ID OrderedDate 110 January 110 February 200 January 200 February How can I write this query? ...

Can you use multiple columns for a not in query?

I recently saw someone post this as part of an answer to an SO query question: SELECT DISTINCT a, b, c FROM t1 WHERE (a,b,c) NOT IN ( SELECT DISTINCT a,b,c FROM t2 ) I'm a bit confused, as I always thought that you can't use multiple columns for "NOT IN" ("where(a,b,c)", etc.). Is this correct SQL syntax? And how about MySQL? ...

mysql query php

i am sending an email to 1000 people. i have a php send email. it works fine. i was wondering, in my query, how can i send it 100 at time. is it like this: select * from test limit 0, 100 select * from test limit 100, 100 select * from test limit 200, 100 select * from test limit 300, 100 etc... thanks. ...

Problem running DELETE statement against linked server in SQL Server 2008

I'm having a problem running this query: DELETE FROM [IRPROD]..[BUDGET_USER].[GL_EXP] WHERE FISCAL_YEAR = 2010 IRPROD is a linked server to an Oracle 10g database. It's linked using the Oracle OleDB provider. There are ~79000 records that need to be deleted. Running this query it deletes 54. Running it again gives me this error messag...

Select highest total amount in different tables (Access)

Hi I have 2 tables: "sales" and "services". Both tables have these fields: customer and amount I need to retrieve the customer with the highest total amount (sum all amounts), between dates., in the both tables. Example: sales Mary | $100 John | $200 Mary | $200 services Mary | $40 John | $300 If we...