I have 3 tables: Users (id, name) Orders (id, userId) Orders_Items (id, orderId, status) At first I wanted to list all users with their respective count of orders like this: A, 1 order B, 5 orders That's easy, I'm doing a "select name, count(id) from users, orders where users.id = orders.userId group by name". Now, I'd like to f...
I have this query: SELECT groups.name categories.name, categories.label FROM groups JOIN categories ON (categories.group1 = groups.id OR categories.group2 = groups.id) AND groups.label = :section AND categories.active = 1 Now, this is my JOIN using Zend_Db_Select but it gives me array error $select...
Hi, I have a datagrid with a button in each row that when I click on it, it redirects to another page. I want to be able to click on the entire row and not on that button. The problem is that now I go to database when I click on the button like so: DataTable dt = DataAccessLayer.selectFromTable(reviewedGrid.Rows[e.NewSelectedIndex].Ce...
Hello, I'm developing an app which requires that the user selects a year formatted like this 1992-1993 from a spinner. The tablename is also named 1992-1993 and the idea is that using SQL the values from this table are pulled through with a statement like this select * from 1992-1993. When I run the emulator however, it throws an error....
When querying a table in sql server, im trying to fetch only the current page of records. However I need the total number of records that would be returned for the particular query to calculate the number of pages. How to do this efficiently without writing another query to count the records. WITH allentities AS (SELECT Row_...
I realise it's possible to pass in a manually constructed String to the execute(String) which is vulnerable. However I'm interested in where you pass the parameters to the query using MapSqlParameterSource or one of the other exposed methods such as in the below examples. Digging into the source it looks like it's using a prepared stat...
Hi I am having the same problem as the OP here: http://stackoverflow.com/q/2305534/485483 My CPU load goes up to 100+ thanks to a WP query like this one: # Query_time: 8 Lock_time: 0 Rows_sent: 9 Rows_examined: 1705 SELECT SQL_CALC_FOUND_ROWS wordpress_posts.* FROM wordpress_posts WHERE 1=1 AND wordpress_posts.post_type = 'pos...
I have two tables: persons - person_id - fullname students - student_id _ person_id - father_id - mother_id In students table the last three columns store ids from persons table. What SELECT could retrieve the following data: - student name - father name - mother name We assume no WHERE, ORDER BY, or LIMIT for simplicity ...
I need some advice regarding an application I wrote. The issues I am having are due to my DAL and connections to my SQL Server 2008 database not being closed, however I have looked at my code and each connection is always being closed. The application is a multithreaded application that retrieves a set of records and while it processes...
Let's say I have a table like this (ordered by id): id amount --- --- 1 10 2 15 3 10 4 30 I want a query which will return rows such that the sum of amount is greater than a given number. So (in a non-existing syntax) SELECT id, amount LIMIT BY running_total(amount) 20 selects first 2 rows, ... LIMIT BY running_to...
I have a complex query I've been wrestling with for 2 days and can't seem to get it to run. I have two querys - The first query works fine but I can't figure out the second What am I doing doing wrong? CREATE TABLE `recipes` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `cat` int(5) NOT...
I have an SQL table called tbl, im trying to add the columns A, B and C to it. When i execute the command : String addcolumns = "ALTER TABLE SqlCreatedTbl ADD A char(50) ;"; ...... cmd = new SqlCommand(addcolumns, conn); conn.Open(); cmd.ExecuteNonQuery(); The column i...
Hi All i am doing a test to host my WPF app on the WEB with a Databinding but when i deply the file i receive this error: Startup URI: C:\Users\Mamma\Desktop\New folder (2)\WpfBrowserApplication1.xbap Application Identity: file:///C:/Users/Mamma/Desktop/New%20folder%20(2)/WpfBrowserApplication1.xbap#WpfBrowserApplication1.xbap, Versio...
I have a little problem - i would can get with this query topics posts count and last post id, but i can't figure out, how to get right user id. I get first post (lowest id) user id but i want lastest post... I have tried adding "ORDER BY id DESC" but this will not help. Any ideas how to do it? SELECT COUNT(`id`) AS `count`, M...
I want to create a two stored procedures that will remove records older than N days from a stgging database. Both stored proc will call in a package to cleanup data older than N days. Stored proc A will remove records from table A.Fullimport & stored proc B will remove records from table B.weeklyimport. The same stored proc will remov...
Hi guys simple question, but can't find anything. Thanks ...
I'm new to database structure. I'm trying to create an app that allows users to like certain entries, but I want to be able to tie likes to users so that I can change the visuals before/after the like action. I think from research that I should have an 'entries' and 'users' table and then have a 'likes' table that ties the two to each o...
Possible Duplicate: how can i write storec proc which to remove old records older than 90 days from table1 and table2 Hi All, i want to create two new stored procedures to remove records older than 90 days from tables DirStaging.Trr.FullEnrollmentImport and DirStaging.Trr.WeeklyMonthlyImport. The same stored procedure will rem...
I am using the following SQL to select records from MySQL database: SELECT * FROM cms_product WHERE id IN (3,22,1); The results order equals "ORDER BY id ASC", so as in example records 1,3,22 are returned. How can I get them ordered in the exact way as typed in IN clause? So ordered as 3,22,1 ? Thank you. ...
Hi all, Have created a stored procedure which is utilised for monitoring purposes of a website. On first run the procedure takes over a minute to execute and if run shortly after this it takes only a few seconds to run. The problem is that the script is scheduled to run at ten minute intervals and each time it runs, it takes over a mi...