sql

Getting data from two different tables into one single table?

I have the following two tables: uid | ABC | ... (bunch of other stuff) ... 1 | val 1 | ... (bunch of other stuff) ... 2 | val 2 | ... (bunch of other stuff) ... 3 | val 3 | ... (bunch of other stuff) ... and... uid | DEF | ... (bunch of other stuff) ... 4 | val 4 | ... (bunch of other stuff) ... 5 | val 5...

Selecting distinct identity rows based on the lowest value of a joined priority column

Simplified table structures, all INT columns and no PKs outside of the identity columns: Nodes (n) table: id Attributes (a) table: id, node_id, type_id Type (t) table: id, priority I'm trying to select a set of attributes, each of which has the lowest type.priority for its respective node. Though there are multiple attributes per nod...

Optimizing SQL 2008 full text query (CONTAINSTABLE)

I've got the following query that uses a full-text index to search for the TOP 5 products (from the RawProducts table) matching the query, in a given Shop (populated by the @ShopId variable). At the moment I'm calling this procedure over and over again for every ShopId (there are 27 Shops) - which is a bit slow. My question is - could ...

Can I test for the existence of a table in a SQLite database?

Is there a way to test for the existence of a table in a SQLite database? Right now, I'm creating the table inside a try catch block, and if that throws an exception I know the table has been created. Surely there has to be a better way, right? ...

SQL Delete low counts

I have a table with this data: Id Qty -- --- A 1 A 2 A 3 B 112 B 125 B 109 But I'm supposed to only have the max values for each id. Max value for A is 3 and for B is 125. How can I isolate (and delete) the other values? The final table should look like this : Id Qty -...

How can I execute a .sql from C#?

For some integration tests I want to connect to the database and run a .sql file that has the schema needed for the tests to actually run, including GO statements. How can I execute the .sql file? (or is this totally the wrong way to go?) I've found a post in the MSDN forum showing this code: using System.Data.SqlClient; using System.I...

Reordering data in a table

If I have this data in a table in MSSQL which represents a horizontal row of objects and their coordinates: row id value 35395 776130 0 35395 776131 20 35395 776132 40 35395 776133 60 35395 776134 80 35395 776135 100 35395 776136 120 35395 776137 140 35395 776138 160 35395 776139 180 35395 776140 200 An...

Django syncdb on SQL initial data using PostgreSQL yields "column ... does not exist"

Platform: Python 2.5, Django development root, PostgreSQL 8.4, Windows Vista Ultimate SP2. Procedure: Django Documentation, Release 1.0, link text, Section 34.2, Providing initial SQL data. CODE: models.py: class aisc_customary(models.Model): MTYPE = models.CharField(max_length=4, editable=False, ...

SQL Server 2008 - boolean variable change query

I have the following query: DECLARE @IsStocked bit SELECT * FROM Products p WHERE p.LastSeen > GETDATE() - 30 This returns all Products that have been seen within the last 30 days. My question is, I would like the p.LastSeen > GETDATE() - 30 clause to only apply when @IsStocked = true. This is part of a larger query, and I'd like...

SQL Server 2008 - excluding rows

Hi, I have the following query DECLARE @ProductIdsToExclude nvarchar(max) SET @ProductIdsToExclude = '49506541-4CE2-40AC-812A-7AB262E6F0B0,49506541-4ce2-40ac-812a-7ab262e6f0b0' I'm then using this function to parse @ProductIdsToExclude to a temp table: CREATE TABLE #TempProductIdsToExclude (ProductId uniqueidentifie...

How to implement a bidirectional unique index across multiple columns

Okay i know i can do this in the application layer, which is probably the easiest thing to do, but just to make sure that no errors water down to the DB, i have a serious question I have two columns X and Y, each to store two integers (either A or B in any of the columns). Is it possible to have a unique index constraint such that, in n...

No idea how to write complex SQL query

Hi I have following db structure: File, User, FileRevision (has foreign key to File, and many-2-many connection through intermediate table to User). I want to fetch all FileRevision-s that: are newest/freshest in their corresponding File-s, have many-2-many link to User that performs search (permission checking). I found out that...

MySQL Error when dropping index (errno 150)

I've got problem with dropping foreign key index, I always get the same error ALTER TABLE `comments` DROP INDEX `id_user` which outputs 1025 - Error on rename of './postuj_cz1/#sql-d834_a0c704' to './postuj_cz1/comments' (errno: 150) The id_user on the other table is simple primary key index. I'm using MySQL version 5.0.85 ...

Need help with a multiple table query in mysql

I'm working on building a forum with kohana. I know there is already good, free, forum software out there, but it's for a family site, so I thought I'd use it as a learning experience. I'm also not using the ORM that is built into Kohana, as I would like to learn more about SQL in the process of building the forum. For my forum I hav...

Enumerated text columns in SQL

I have a number of tables that have text columns that contain only a few different distinct values. I often play the tradeoff between the benefits (primarily reduced row size) of extracting the possible values into a lookup table and storing a small index in the table against the amount of work required to do so. For the columns that h...

SQL Server 2008 - limiting query by FK

I have the following query, which performs a full-text search (CONTAINSTABLE) on the Products table, and returns 1 record. Each Product is referenced by the ShopProducts table (each ShopProduct represents a product in a shop and is has a foreign key to Products.ProductId. Each row in the ShopProducts table has a ShopId column. My quest...

SQL 2005 MD5 Hash and C# MD5 Hash

I currently have a legacy database (SQL 2005) that generates hash strings for tokens. It does it like this... DECLARE @RowID INT DECLARE @hashString VARCHAR(128) SET @RowID = 12345 SET @salt= 0xD2779428A5328AF9 SET @hashBinary = HASHBYTES(('MD5', @salt + CAST(@RowID AS VARBINARY(30))) SET @hashString = sys.fn_varbintohexstr(@hashBina...

How to utilise indexes in SQL

I'm building a rather large database - which has around 6.9 million records. A simple select is taking 6-7 seconds, so I'm now working on optimising and investigating other options. An obvious one is to create an index or two. Sample: CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME) That worked well. However, I cannot get ...

Database design question

I'm currently developing a website for a hotel. And one of the things I'm about to implement is worker->superior relationship. What is the best way to do so in MySQL? Here is what I mean: a chef's superior is a head chef, head chef's superior is shift manager, shift manager's superior is general manager. In the employee table, I could m...

How create SQL query that validates friends of friends.

Hello. I have two tables. USER user_id password FRIEND_LIST user_id friend_id If user 1 is friend of user 2 then in friend_list there will be 2 records: 1 2 2 1 Thats how I'm controlling friend list. My question is how can I create an efficient query that validates if a user is a friend of a friend. For example user 1 has in his...