sql-query

select arbitrary amount of rows with N distinct values in foreign key referenced column

The database schema is CREATE TABLE sites ( site_id INTEGER PRIMARY KEY AUTOINCREMENT, netloc TEXT UNIQUE NOT NULL, last_visited REAL DEFAULT 0, crawl_rate REAL DEFAULT 2, crawl_frequency REAL DEFAULT 604800, robots_txt TEXT DEFAULT 0, robots_last_fetch REAL DEFAULT ...

How to remove not null constraint in sql server using query

I am trying to remove not null constraint in sql server 2008 without losing data. ...

SQL Query to retrieve data while excluding a set of rows

Hi All, I have basically four tables (SQL Server): Objects: id ObjectName Components id ComponentName ObjectsDetails: ObjectID ComponentID ExclusionTable id ComponentID Basically, these tables describe Objects and what Objects are made of (what components) For example, Object "A" may be made out of component "A" and component "B...

Efficiently pulling thousands of records with SQL Server?

This is kind of a part 2 to this question: http://stackoverflow.com/questions/3319842/select-rows-and-update-same-rows-for-locking I have a table in my database that hold millions of records. I have created a stored procedure to pull X amount of records and mark them as being locked so when my application calls the next set of X amount ...

Get index of row within a group?

Hello! I have two tables: Unit: UnitId int PK Title varchar UnitOption: UnitOptionId int PK UnitId int FK Title varchar Quote: QuoteId int PK UnitOptionId int FK Title varchar I want to create a scalar UDF that takes a QuoteId param and returns a varchar that contains the following description (pseudu): Quote.Title + '-' + Unit.Ti...

Why the Select * FROM Table where ID NOT IN ( list of int ids) query is slow in sql server ce?

well this problem is general in sql server ce i have indexes on all the the fields. also the same query but with ID IN ( list of int ids) is pretty fast. i tried to change the query to OUTER Join but this just make it worse. so any hints on why this happen and how to fix this problem? ...

MySQL -- Better way to do this query?

This query will be done in a cached autocomplete text box, possibly by thousands of users at the same time. What I have below works, bit I feel there may be a better way to do what I am doing. Any advice? UPDATED -- it can be 'something%': SELECT a.`object_id`, a.`type`, IF( b.`name` IS NOT NULL, b.`name`, IF( c.`name` IS...

how to use having clause in sql query in my result set that I am getting

SELECT P.PK_PatientId ,PV.PK_PatientVisitId --, PV.LastUpdated , ISNULL(P.FName,'')+ ', '+ ISNULL(P.LName,'') AS NAME , MAX(TVP.PK_VisitProcedureId) AS PK_VisitProcedureId , MAX(PV.LastUpdated)AS DateSort FROM dbo.M_Patient AS P ...

How do I define a NHibernate sql-query using mapping attributes in C#?

So for a project that we are working on we have our entire domain model mapped using mapping-attributes on the business entities themselves. I have a working sql-query mapping that I would like to define using the mapping-attributes but I am not sure how to do this. Any help is appreciated. Here is the xml mapping that needs to be trans...

SQL - Make this statement faster i.e. less duration, reads

Given the following tables: Orders (OrderID, OrderStatus, OrderNumber) OrderItems(OrderItemID, OrderID, ItemID, OrderItemStatus) Orders: 2537 records Order Items: 1319 records I have created indexes on Orders(OrderStatus) OrderItems(OrderID) OrderItems(OrderItemStatus) I have the following SQL statement (generated by LinqToSql) ...

SQL query for insert into with a set of constants

It seems like there should be a query for this, but I can't think of how to do it. I've got a table with a composite primary key consisting of two fields I'd like to populate with data, I can do an insert into from one table to fill up half the keys, but I want to fill up the other half with a set of constants (0, 3, 5, 6, 9) etc... s...

error in mysql query, does not return MIN value...

I'm trying to get this query to work. The query was written by "OMG Ponies" as an answer to: http://stackoverflow.com/questions/3796228/fix-mysql-query-to-return-random-row-within-subgroup The query below calculates correctly the difference in dates, but then fails to select the ROW (within ID1-ID2 pairs) with the minimum value of tha...

Table Recovery Question

Suppose that I have a table with the following schema: tableId field1 field2 ..... I have two copies of my database (backup and production). On the production instance, a query was accidentally run which did the following: Update table set field2 = null where field1 = 'x'; I am trying to undo this query based on the data stored ...

How to use OR in MySQL query and ignore second condition if first is true?

I am using a query like this: SELECT * FROM table1 WHERE countries LIKE '%US%' OR countries LIKE '%ALL%'; This returns all the entries that have "US" or "ALL" in countries column, but I want it to return ONLY those rows that contains "US", and if non is found then use the next condition and return all that contains "ALL". Is it possi...

pick next 24 hours job

retrieve next 24 hours details from the table i read one thread, same question here link text but small diff is , my field name is jdate is the varachar, but they are used the datetime data type format, ...