sql

Need help with database connection and query code

In my code below, the cmdquery works but the hrquery does not. How do I get another query to populate a grid view? Do I need to establish a new connection or use the same connection? Can you guys help me? I'm new to C# and asp. Here's some spaghetti code I put together. It may all be wrong so if you have a better way of doing this feel ...

Get threads linked to tags

I have 3 tables in my MySQL database : the Threads table (id, title) the Tags table (id, name) the pivot table ThreadTags (tagID, threadID) Let's admit I already have the tag IDs in array $tagIDs, now I want to have all threads linked to ALL those tags. My current solution is something like that : $stmt = 'SELECT id, title FROM Th...

TSQL help with statistics query

I've made a post here a link , asking some question about how to make statistics for number of users with from same age group so i can show pie chart. I want to take query in older post to higher level with percentages along with values... Means i to have count(userID) of certain age group / count(userID) of all * 100 to get percentage....

Oracle to_date SQL formatting odd behavior

I've trawled the SO website thinking this is pretty obvious but alas I have not found one yet. Basically the setup is as follows: I have a string in a table like so: 06/22/2010 00:00:00 I do a select on it: SELECT To_Date(item, 'MM/DD/YYYY HH24:MI:SS') from that_table so that effectively I am doing SELECT To_Date('06/22/2010 0...

Title: CTE error

I am getting these errors while executing the following SQL please help me ;WITH myCTE AS (Select mcisi.* from coke_packaged_item AS spi JOIN coke_item AS si ON si.coke_id = spi.coke_id AND si.coke_item_id = spi.coke_item_id AND si.shipper_flag = 'n' JOIN merch_cat_import_coke_item AS mcisi ON mcisi.coke_id = si.coke_id AND m...

One Row, Multiple GROUPs

I have a list of reservations in a MySQL db, and I want to determine total number of slots reserved in 15 minute intervals. To do that, I need to 'unroll' (probably not the right word) each reservation, determine the overlap, and show total usage. The table is a list of reservations: ResID StartTime EndTi...

simple & fast sql server database backup and restore

Hi, I'm looking for a simple (simplest if possible) way of backing up and restoring a database. I want to do a backup in one state and then after doing some operations get back to the backed up state. Tried Database->Tasks->Back Up... and then Database->Tasks->Restore but I always get an error with: Restore failed for...DBName The t...

SQL Join Statement

I have a simple mysql query which is as following: SELECT DISTINCT(node.nid) AS nid, node_counter.totalcount AS node_counter_totalcount, node.title AS node_title, node_data_field_dep.field_dep_value AS node_data_field_dep_field_dep_value, node.type AS node_type, node.vid AS node_vid, node_data_field_type.field_type_value AS node_data_fi...

SQL: How do I make a selection based on categories?

There are two tables, categories and books and I'd like to select all books based on the given categories. Categories table: cat_id | book_id ---------------- 1 | 1 2 | 1 3 | 1 3 | 2 Books table: id | name ---------------- 1 | abc 2 | def I've tried SELECT * FROM categories WHERE cat_id IN(1,3) but then it...

Hibernate, SQL and recursive associations

My database has two tables, "question" and "field". Questions may have many fields, and fields may have many fields. It's a tree with a special root node. I want to use them with hibernate (currently potgresql) - so it should be straightforward and simple to use it from java. What is the best solution to this? add question_parent_id ...

Searching table by Guid faster when the Guid is the clustered index?

If I am going to be querying a table by Guids (irregardless of fragmentation problems with Guids), would it be faster to have the Guid as the clustered index rather than the non-clustered index or no index at all? This question is coming from a read-only standpoint. I'm just curious if there will be a speed improvement between the searc...

create select statement based on case statement in sql

Is there a clean way in sql to create a select statement based on a case statement? I'm looking for the basic logic to be something like CASE @tableToUse WHEN 'Table1' SELECT * FROM Table1 WHEN 'Table2' SELECT * FROM table2 DEFAULT 'Table3' SELECT * FROM table3 END CASE ...

Stored procedure returning the result of an UPDATE

I have a table that is used to store an incrementing numeric ID (of type INT). It contains a single row. The ID is incremented using a query: UPDATE TOP(1) MyTable WITH(TABLOCKX) SET NextID = NextID + 1 I would like to move this into a stored procedure that returns the value that was in the NextID column before it was incremented, but...

Is it possible to call a function from and dotnet asseble from a MS SQL query?

Is it possible to call a function from and dotnet asseble from a MS SQL query? ...

Inserting text string with hex into PostgreSQL as a bytea

I have a text file with several strings of hex in it: 013d7d16d7ad4fefb61bd95b765c8ceb 007687fc64b746569616414b78c81ef1 I would like to store these in the database as a bytea, instead of a varchar. That is, I would like the database to store 01 as the single byte 00000001, not characters '0' & '1'. I can easily run this file through ...

SQL Server Delete Trigger To Update Multiple Columns in Same Table

I have the following trigger: CREATE TRIGGER Users_Delete ON Users AFTER DELETE AS BEGIN SET NOCOUNT ON; -- Patients UPDATE Patients SET ModifiedByID=NULL WHERE ModifiedByID=ID; UPDATE Patients SET CreatedByID=NULL WHERE CreatedByID=ID; UPDATE Patients SET DeletedByID=NULL WHERE DeletedByID=ID; END I wa...

Query Total Amounts for Orders where the amount of each item was greater than >100

Using the following two tables on SQL Server 2005, how would I write a query to return the First_Name, Last_Name, Order_Date and total amount of an order where any order line item amounts for any order (OD_Amount) are greater than 100. Orders Order_ID First_Name Last_Name Order_Date Order_Details Order_Detail_ID Order_ID OD_Item_N...

union versus or

let's say I've got two queries: select top 20 idField, field1, field2 from table1 where idField in ( select idField from table1 where field3 like '...' union select idField from table2 where field4 like '...' ) order by sortfield1 select top 20 idField, field1, field2 from table1 where field3 like '...' or idfield ...

SELECT DISTINCT for multiple fields

In my table, I have Collection_Date and Tag_Date fields, both of type date. I need to query this table, separating each date into its component month, day, and year, but still keeping the distinction between the Collection_Date dates and the Tag_Date dates, and I don't want duplicates. I tried this: SELECT DISTINCT MONTH(Collectio...

SQL unique query -- need help

Hello, Need some help with what is probably a pretty basic SQL query. I'm trying to select the number of unique records that match a specific where clause. Essentially, I'm looking to find the number of unique users that logged in between date x and y. If a user logged in more than once between those dates, it would need to only count ...