I have a table, let's call it 'entries' that looks like this (simplified): id [pk] user_id [fk] created [date] processed [boolean, default false] and I want to create an UPDATE query which will set the processed flag to true on all entries except for the latest 3 for each user (latest in terms of the created column). So, for the follo...
Think about a table like this ID Value 100 1 100 3 101 1 101 4 102 2 102 5 103 2 103 4 104 1 104 3 105 2 105 5 The problem is, if I give values 2 and 5 I should get 102 and 105 which both 102 and 105 are having values 2 and 5 at the same time or if...
I just wanted to share this Query class and get your thoughts on it. The Query class helps execute queries against a dbconnection. I haven't included the implementation, it's a bit much to post. Here's an example call, you'll get the idea: OrdersDataTable table = new Query(connection) .Table("Orders") .Fields("OrderID,Customer...
Hi guys, I am having trouble with a SQL join question. I have a table EMPLOYEE with EmpID, FirstName, LastName, Email, Phone I have another table OTHERNAME with 2 fields "Name" & "OtherName". This table contains lookup values such as "James", "Jim"; "Thomas", "Tom"; "Steven", "Steve". I want to write a query which will return rows E...
I am trying to create a query to sum total gifts given by a certain person, but to add a flag on the individual gift when the total giving hits $500 (I don't need to sum past that). I have the Persons ID, the gift size, and the gift date. for example: ID gift_date gift_amt --------- ----------- -------------- 1...
Here's what I'm trying to do. User (a): Enters data in two fields (description-1) and (description-2). User (b) Enters similar data in opposite fields. User (a) or (b) search on both fields would find a match. A good analogy would be a dating search. User (a) enters a description of themselves and the match they are looking for, and...
table structure id | message | reply_id 1 | help me! | 0 434 | love to | 1 852 | didn't work | 434 0110 | try this | 852 2200 | this wont | 0 5465 | done. :) | 0110 i have a id "852" which is middle of tree menu, but i want to get all the previous related and next re...
CONTEXT I have a list of items (or arbitrary length). I'd like to group them in 'chunks' of a certain size Example: I have 12 customers [0,1,2,3,4,5,6,7,8,9,10,11] and want to group themin chunks of 5 which would give [0,1,2,3,4] [5,6,7,8,9] [10,11] NOTE: In reality I am not working with customers or monotonically increasing integers. ...
id | photo title | created_date XEi43 | my family | 2009 08 04 dDls | friends group | 2009 08 05 32kJ | beautiful place | 2009 08 06 EOIk | working late | 2009 08 07 Say I have the id 32kJ. How would I get the new or previous row? ...
Hi Gurus, I was searching for connecting to database from C program. But I thought the ODBC connections, logon and all need some libraries. Also I am having a minimal compiler like Tiny C Compiler which is very fast. I do not want to use any ODBC logic etc which is needed to connect and query the database. So I am using a method which is...
I'm trying to query the latest entries on two different tables as one query: SELECT news.title AS news_title, news.sid AS news_id, downloads.lid AS download_id, downloads.title AS download_title FROM news, downloads ORDER BY news_id DESC, download_id DESC LIMIT 0,10 The query is really slow and it s...
Hey, I've been thinking about this one for quite some time now, I need a way to add replies to comments in the database but I'm not sure how to proceed. This is my currently comment table (doesn't say much but its a start): CREATE TABLE IF NOT EXISTS `comments` ( `id` int(12) NOT NULL AUTO_INCREMENT, `comment` text, `user_id` in...
I've written a query in MS Access. This is a simplified version: SELECT IIf([category] LIKE "*abc*","DEF",category) AS category , Month , Sum(qty) AS [qty] FROM [tableX] GROUP BY category, Month The purpose of the query is to sum quantities of a product in different categories for different months. I want to aggregate categories lik...
This question is best phrased with a simple example. Why can't I do this? select (lastname + ', ' + firstname) as fullname from people where fullname = 'Bloggs, Joe' instead I have to do this: select (lastname + ', ' + firstname) as fullname from people where (lastname + ', ' + firstname) = 'Bloggs, Joe' which smells bad to me. T...
I want to use a query as following, I am looking for exact information/link to escape strings BookTitle is NVARCHAR(200) SELECT * FROM Books WHERE BookTitle IN ('Mars and Venus', 'Stack''s Overflow \r\n') Question: Does only "'" needs to be escaped or even \r\n needs to be escaped as well? MySql .Net Provider exposes a method to esca...
On my site, people can buy parts for vehicles. I want to create a report for a customer to see how much they have spent on parts per month on the site, and allow them to filter by date. If I was writing it in SQL, I would write this: SELECT v.id, DATEPART(YEAR, o.order_date), DATEPART(MONTH, o.order_date), SUM(i.unit_...
Is it possible to convert a properly formed (in terms of brackets) expression such as ((a and b) or c) and d into a Regex expression and use Java or another language's built-in engine with an input term such as ABCDE (case-insensitive...)? So far I've tried something along the lines of (b)(^.?)(a|e)* for the search b and (a or e) ...
I have a query which is crossing two tables select count(*) from ingenium.empevt, ingenium.evt where empevt_evtfk = evt_pk it takes quite a long time to run and in trying to figure out why I looked at the plan Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=CHOOSE 634 K ...
Hi, I have a lengthy query here, and wondering whether it could be refactor? Declare @A1 as int Declare @A2 as int ... Declare @A50 as int SET @A1 =(Select id from table where code='ABC1') SET @A2 =(Select id from table where code='ABC2') ... SET @A50 =(Select id from table where code='ABC50') Insert into tableB Select Case when @A1...
Hi ! Considering the following mapping file where TemporaryAccessOpenCommand and TemporaryAccessCloseCommand both inherit from base class Command <class name="Command" table="xxx_Commands" lazy="false"> <id name="Id" type="Int32" unsaved-value="0"> <generator class="identity"/> </id> <property name="BeginDate" /> <property name="EndD...