I've got a database with 3 tables: users, groups, and groupmembers. users: id int username varchar(50) groups: id int name varchar(50) groupmembers: id int groupid int user int Given two user ids, how can I use one query to determine if they share membership in any group (not any particular one, but any group). I'd like the query t...
Hello, I am passing a comma-delimited list of values into a stored procedure. I need to execute a query to see if the ID of an entity is in the comma-delimited list. Unfortunately, I think I do not understand something. When I execute the following stored procedure: exec dbo.myStoredProcedure @myFilter=N'1, 2, 3, 4' I receive the f...
Apparently i need two queries to get the data i need. I cant give out to much information but is it possible to write 2 queries in one statement? (I have a feeling it is possible). Using mysql 4 ...
I need to be able to read a combo box to determine to column to use for a where clause. Simple example would be SELECT * FROM TABLE WHERE [Forms]![frmNameWhatever]![ComboTime] BETWEEN [blah]![blah]![blah] AND [blah]![blah]![blah] blah blah blah works... The first part, right after the where, returns zero rows... Am i using the wrong syn...
I have a text column containing URLs that are uniqe. I need to do a SQL pattern match query, say using the 'like' SQL operator. In such a scenario, will SQLite do a FTS ( Full Table Scan ) regardless of whether I have an index on that column or not ( column is a primary key )? It seems to be that its doing a FTS as the speed of operat...
My SAMPLE table has the following five columns: sample_id (PK) (NUMBER) sampled_on (DATE) received_on (DATE) completed_on (DATE) authorized_on (DATE) I would like a query with one row per hour (constrained by a given date range) and five columns: The hour YYYY-MM-DD HH24 Number of samples sampled during that hour Number of samples r...
Hello all, I'm having trouble getting my user defined function operating properly. I am running on SQL Server 2000. I am trying to return a table of all users that have an even balance in a "BillingTransactions" table. Our transactions are specified by the RecordType field; 0 for purchase, 1 for payment. So what I'm trying to do is get...
This query is really slow. Takes between 9 and 10 seconds... SELECT DISTINCT a.* FROM addresses a LEFT JOIN contacts c ON c.id = a.contact_id LEFT JOIN organizations o ON o.id = a.organization_id ORDER BY c.last_name, c.first_name, o.name LIMIT 0, 24 If I comment out the ORDER BY clause the query runs much faster -- about 5 millisecon...
I'm trying to return the Id of a row I update in sql UPDATE ITS2_UserNames SET AupIp = @AupIp WHERE @Customer_ID = TCID AND @Handle_ID = ID SELECT @@ERROR AS Error, @@ROWCOUNT AS RowsAffected, SCOPE_IDENTITY() AS ID and I keep getting Null for the ID, how can I get this? ...
How can I return what would effectively be a "contiguous" GROUP BY in MySQL. In other words a GROUP BY that respects the order of the recordset? For example, SELECT MIN(col1), col2, COUNT(*) FROM table GROUP BY col2 ORDER BY col1 from the following table where col1 is a unique ordered index: 1 a 2 a 3 b 4 b 5 a 6 a ...
Given a self referencing table Item ------------- Id (pk) ParentId (fk) With a related table of associated values ItemValue ------------- ItemId (fk) Amount And some sample data Item ItemValues Id ParentId ItemId Amount -------------------- ---------------------- 1 null ...
Hello, I need to convert my query to classic Oracle join syntax because I need to put it in a materialized view. However, I'm not quite sure how to do it: SELECT * FROM transactions t LEFT JOIN transac_detail td1 ON (t.id = t1.trans_id AND t.ttype = 'VOICE') LEFT JOIN transac_detail td2 ON (t.id = t2.trans_id AND t.ttype = 'BROADBAND')...
Hi there, I have a table in MS Access database and need to create a query to trim down the result. For example: here is the table: ------------------------------------- search code | relation | environment | ------------------------------------- Server.PRD | installs | Production | ------------------------------------- Server.DEV |...
I'm just wondering, as a hypothetical example, what would be the best way to layout a table for the following scenario: Let's say I'm writing an app for tracking student attendance. At the beginning of every year, I want to add all the students in (I'll do this manually - now, should a student ID be assigned to each one here? Let's call...
create table jobs( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, ..... salaryminus INTEGER UNSIGNED DEFAULT NULL, salaryplus INTEGER UNSIGNED DEFAULT NULL, ..... ); I want to do something like : Select * from jobs order by maxof(salaryminus, salaryplus) limit 10; maxof(Null,1000) should be 1000, H...
Hi , I have a dataset with some 30 records in it. I want to update it to the database tables. which is the best method to update the table. I am unable to use dataadapter.update() since i am using a procedure to populate the dataset. is there any efficient way to update other than iterating through EACH record ...
How can I write a select statement to select only integers (and nothing more) from a char column in SQL Server. For example, my table name is POWDER with 2 columns, ID (int) and Name(char (5)) ID Name -- ---------- 1 AXF22 2 HYWWW 3 24680 4 8YUH8 5 96635 I want to be able to select only those rows that contain...
I have a service to update/inserts 7000+ rows using MARS. Is there a better way to do this? ...
I'm having to insert values into a new column in our database but I can't get my head around doing this in a consistent manner. There is a lot of data so doing anything manually is pretty much out of the question. Let me set the stage: We have a table called Occurrence and a table called OccurenceBuckets where each occurrence is referen...
Hi I have the following SQL: SELECT * FROM Name INNER JOIN ( SELECT 2 AS item, NameInAddress.NameID as itemID, NameInAddress.AddressID FROM NameInAddress INNER JOIN Address ON Address.AddressID = NameInAddress.AddressID WHERE (Address.Country != 'UK') ) AS Items ON (Items.itemID = Name .Name ID) I have been asked to r...