sql

Auto Index in My SQL

Hi. I am using MySQL for my database. I have a requirement to store a list of users in the system. The user will have both first name and last name. I cannot have the first name and second name as the primary key. So I need a indexing key. But I do not want to enter the index value every time to add a new row. I want the system to ha...

Execution plan oddity after re-enabling foreign key constraint

I have a weird problem where after setting nocheck on a foreign constraint and re-enabling it, I am getting a same out-dated execution plan that was used with nocheck on. Why would SQL server generate an execution plan as if foreign constraint FKBtoA is disabled even after adding the check again with following statement? alter table ...

SQL Query Conversion to LINQ Left Outer Join (VB.NET)

I've looked all around and spent way to long trying to convert this SQL statement into a Linq statement in VB. I'm sure it would be a good example for others out there - the statement is trying to pull products that have a many-to-many relationship with product categories, and the categories have a hierarchy of parents/children. Here is...

Why are positional queries bad?

I'm reading CJ Date's SQL and Relational Theory: How to Write Accurate SQL Code, and he makes the case that positional queries are bad for example, this INSERT: INSERT INTO t VALUES (1, 2, 3) Instead, you should use attribute-based queries like this: INSERT INTO t (one, two, three) VALUES (1, 2, 3) Now, I understand that the first...

SQL Server - standard pattern for doing row by row operations on a table/view

I want to iterate through a table/view and then kick off some process (e.g. run a job, send an email) based on some criteria. My arbitrary constraint here is that I want to do this inside the database itself, using T-SQL on a stored proc, trigger, etc. Does this scenario require cursors, or is there some other native T-SQL row-based ...

Url replacing

I'm trying to make some nice urls. CREATE TABLE IF NOT EXISTS `games` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `about` text, `release_date` int(10) NOT NULL DEFAULT '0' php $id = str_replace('_', ' ', $_GET['title']); $query = mysql_query("SELECT * FROM games WHERE title = '$id'"); $row = my...

How would I do a join of three tables with sql server

Hello, I'm trying to figure out what to do here. Look at these tables: users id, username, password books id, title users_books bookID, userID How would one join these together? If I for example wanted to see user 47's books? ...

SQL Get first not null value from Parent Rows

Hey everyone, I've come across this problem a few times now, and I would like to find a better way to solve the problem. Basically I have a Category which has Sub Categories. If the Sub Category does not have a Description I would like to retrieve the Parent Category Description. It starts to get difficult when I have Sub Sub Categor...

Filtering a ComboBox by available values

I have a nice DataGridView showing what is basically some kind of log data to the user. The user can filter by district, region, settlement and street. So far, the ComboBoxes are linked to each other, so at first only the district has values, and only after picking one, the region one is filled with the proper values, etc. The new req...

Does NEWID() disturb the ROW ORDER of RECORDS?

Hi, This question is just for a sake of knowledge. I don't have any practical implementation of this. I have fired a query like SELECT RAND() RANDOMCOLUMNS,COL1 FROM tblY ORDER BY RANDOMCOLUMNS Output: RANDOMCOLUMNS COL1 ================================ 0.567773034904758 1 0.567773034904758 2 0.567773034904758 3 ...

Unable to inject smalldatetime into D-SQL statement

Hi, when i try to execute this sql statement i am getting the error.. Conversion failed when converting character string to smalldatetime data type. Does anyone know what i am doing wrong? declare @modality varchar(50) declare @datefrom smalldatetime set @modality = 'xxxxxxx' set @datefrom = '20090101' declare @var1 nvarchar(4000) se...

Understanding how JOIN works when 3 or more tables are involved. [SQL]

I wonder if anyone can help improve my understanding of JOINs in SQL. [If it is significant to the problem, I am thinking MS SQL Server specifically.] Take 3 tables A, B [A related to be by some A.AId], and C [B related to C by some B.BId] If I compose a query e.g SELECT * FROM A JOIN B ON A.AId = B.AId All good - I'm sweet with ho...

maintaining a record of sql inserts

Is it possible with a mysql script, full of just mysql commands that get filtered into the mysql binary, to do a count of current records in insert into a stats table, perhaps with the time and date automatically generated? I would want to do this, so calculations could be done, eg work out the total number of new records inserted in a...

How do I create an If-Then-Else in T-SQL

I have some negative values coming back from a query. I would like them to just be zero. How do I write a condition in my sql query that returns zero if the value is below a certain value. sol: CASE WHEN CONVERT(float,dt.FQI53X02_101) < 1.3 THEN 0 ELSE CONVERT(float,dt.FQI53X02_101) END AS FQI53X02_101 ...

Conditional sorting in MySQL?

I have "tasks" table with 3 fields: date priority (0,1,2) done (0,1) What I am trying to achieve is with the whole table sorted by done flag, tasks that are not done should be sorted by priority, while tasks that are done should be sorted by date: Select * from tasks order by done asc If done=0 additionally order by priority desc I...

Deleting a row in MySQL

I have a table: NACHRICHT_ID | VERFASSER_USERNAME | BETREFF | TEXT | DATUM | EMPAENGER_ID ------------------------------------------------------------------------------ | | | | 2009-07-01| 1 | h | hfgh | hfgh | 23:15:10 | 31 --------------------------...

Is there a better way to search over a range of values in Oracle than testing against a subquery?

given this table: x y -- - 10 a 20 b 30 c I want the best way to to map values [10,20) -> a [20,30) -> b [30,inf) -> c Right now I'm using a query like: select y from foo where x=(select max(x) from foo where x<=21); Is there a better way to do this? Is there an analytic function that might help? Here's my te...

SQL: How to make sure any primary key in any table "belongs to" a known Account ID (also PK) at the top of the hierarchy?

Problem as follows: I'm in charge of a creating a web services interface to a system with a huge database (hundreds of tables). The top level table in the database is "Accounts", with primary key Account_Id. Each row in any table can ultimately be traced back to a single account. Each account should have access to their own account v...

Optimally querying a database of ratings?

I have a table of ratings that stores a user ID, object ID, and a score (+1 or -1). Now, when I want to display a list of objects with their total scores, the number of +1 votes, and the number of -1 votes. How can I do this efficiently without having to do SELECT COUNT(*) FROM rating WHERE (score = +/-1 AND object_id = ..)? That's two ...

Finding a timeline using a range of values - entity modeling

Let's say I have two entities: Event and Activity An Event is something that happens at (seemingly) random times, like Sunrise, Sunset, Storm, Fog, etc. I have a table for this: create table Event ( eventKey int, eventDesc varchar(100), started datetime ) EventKey | EventDesc | Started 1 "Sunset" 2009-07-03 6:51pm ...