sql

Can a database index span tables? I'm on Postgres

Is it possible to create an index that has as one of its columns values from another table? Example: model Pet primary_key id foreign_key Species end model Species primary_key id int genus end Assume there's a lot of kinds of species, with fewer types of genuses. I'd like to create an index on the Pets table by Genus. Can i...

C# SQL Statement transformed TO LINQ how can i translate this statement to a working linq

I am having trouble with this I have 3 Data tables i use over and over again which are cached I would like to write a LINQ statement which would do the following is this possible? T-SQL VERSION: SELECT P.[CID],P.[AID] ,B.[AID], B.[Data], B.[Status], B.[Language] FROM MY_TABLE_1 P JOIN ( SELECT A.[AID], A.[Data], A.[Status], A....

mysql: Duplicate entry '0' for key 'PRIMARY' and bizarre ID behavior

See log below. (Snipped just for brevity; unsnipped @ http://pastebin.com/k9sCM6Ee) In short: somehow rows are getting assigned ID 0. When this happens, it blocks inserts, even when those inserts aren't actually conflicting with ID 0 (although that really shouldn't happen in the first place). Although it is heavily read and very heavil...

How to start with an R-tree index in IBM Informix

I have an assignment in which I need to build R-tree indexes on a table and query them. But I am not getting proper tutorial or guide which specifically deals with R-tree in IBM Informix and querying an R-tree. I tried to Google but without much success. Can anyone can provide me with a good startup? ...

Unable to get my master & details gridview to work.

I'm unable to get this to work. I'm very new at programming and would appreciate any help on this. <%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt; <script runat="server"> protected void Page_Load(object sender, EventArgs e) { } pro...

Three phases of recovery : the analysis phase, the redo phase, and finally the undo phase.

SQL Server database recovery consists of three phases: the analysis phase, the redo phase, and finally the undo phase. Can anybody please explain clearly or give a good link on these three phases of recovery?? ...

How to alter the boolean value in SQL Server select query?

Basically I want to alter the boolean value selecting from the table: e.g.: SELECT otherColumns, not ysnPending FROM table I need a column ysnPending = true if the value is false & false if the value is true. Is there any function available to alter the Boolean value or I should use IIf or CASE...? ...

how to insert data using multiple tables

i have created a db names movielibrarysystem in which i have 3 tables.. that are type,publisher and movie... now 1 publisher could have many many movies and 1 movie is of many type.. in the movie table, the publisher id as well as the typeid are acting as a foreign keys.. my question is that how to insert a data into a movie table... ...

SQLite or flat text file?

I process a lot of text/data that I exchange between Python, R, and sometimes Matlab. My go-to is the flat text file, but also use SQLite occasionally to store the data and access from each program (not Matlab yet though). I don't use GROUPBY, AVG, etc. in SQL as much as I do these operations in R, so I don't necessarily require the dat...

mySQL Variables in SELECT

Is it possible to have a query such as the following: SELECT @threadIDvar = `threads`.`id` AS `threadID`, (SELECT `posts`.`timeDate` FROM `posts` WHERE `posts`.`threadID` = @threadIDvar) AS `postDate` FROM `threads` INNER JOIN `posts` ON `posts`.`threadID` = `threads`.`id` WHERE `threads`.`boardID` = 1 I have tried it but I get @threa...

Selection of rows related to a set of other rows.

Let's say, I have two tables, "entities" and "tags". Relation between them is expressed by third table "entity_tags", which have tho fields: entity_id and tag_id. And I want to select all entities sorted by it's relevance to the given set of tags. For example, there is "banana", "orange", "potato", "pen" and "car" as entities and I want ...

How to use ORDER BY with proper handling of numbers?

I want to get data from mysql table sorted by one of it's varchar column. So let's say I have query like this: SELECT name, model FROM vehicle ORDER BY model The problem is, that for 'model' values like these: 'S 43', 'S 111' the order will be: S 111 S 43 because I suppose ORDER BY uses alphabetic order rules, right? So how to modi...

check constraint custom message

Hi. Is there a way to perform data validation using CHECK constraints in t-sql and to show somehow the data which did not pass the check constraint? ...

Help me to write an sql-statement to do something like this

You can also write hibernate hql or criteria queries. I have Teacher entity and Student entity like this : class Teacher { public Long id ; public Set<Student> students; } class Student { public Long id ; public Teacher teacher ; public Boolean passedSemester1; public Boolean passedSemester2; } You can ...

How to write MySQL full-text query with MATCH(), ROUND() & Subquery?

Currently, I am using this query, which does a pretty good job of finding the most relevant results and ordering by "most relevant" first: SELECT *, MATCH(text) AGAINST('$keyword') AS score FROM table_name WHERE MATCH(text) AGAINST('$keyword') ORDER BY score DESC LIMIT $s, 20 Which produces results like: text ...

I'm at a loss what to read and study! Database design or Code Complete?

I've been a PHP programmer for 3 years. I really want to improve myself and I think reading more helps, but I'm confused where to concentrate next. I have two choices, read more about database design and advanced SQL or should I read Code Complete fully? ...

How to transcribe SQL to LINQ

select * form autor inner join (carte inner join carte_autor using id_carte) using id_autor group by id_autor; How can I write this using LINQ? Thanks. ...

Postgres hashing user defined types

Note: m4 is being used so "_" prefixed strings are expanded (m4 is a macro pre-processor similar to the c preprocessor). My Type: CREATE TYPE UrlPair AS ( HostName varchar( _LIMIT_HOSTNAME ), ScriptName varchar( _LIMIT_SCRIPTNAME ) ...

sql query to get earliest date

if i have a table with columns id, name, score, date and i wanted to run a sql query to get the record where id = 2 with the earliest date in the data set. can you do this within the query or do you need to loop after the fact ? EDIT: To be explicit, I want to get all of the fields of that record . . ...

SQL Server 2000 - Breaking out of a loop

Hello, I am not good at SQL Server 2000. I have a comma-delimited list of ids. I need to see if that ID exists in a table. If it does, I want to break out of the loop with that ID saved in a variable that I can use in my stored procedure. This is what I am trying right now: DECLARE @coreID INT SET @coreID=NULL DECLARE @itemID NVARCHAR...