sql

LIKE work-around in SQL (Performance issues)

I've been reading around and found that using LIKE causes a big slowdown in queries. A workmate recommended we use Select Name From mytable a.Name IN (SELECT Name FROM mytable WHERE Name LIKE '%' + ISNULL(@Name, N'') + '%' GROUP BY Name) in lieu of Select Name From mytable a.Name LIKE '%' + ISNULL(...

SQL exeption was unhandled in c#

When I want to debug this code it gives error on objConnection.Open(): sqlExeption was Unhandled and say(A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is con...

What is the preferred way of saving dynamic lists in database?

In our application user can create different lists (like sharepoint) for example a user can create a list of cars (name, model, brand) and a list of students (name, dob, address, nationality), e.t.c. Our application should be able to query on different columns of the list so we can't just serialize each row and save it in one row. Shou...

How do I SELECT an un-referenced row from a table and lock it?

Hi there, I have a table of chalets where a chalet is referenced by an account... CHALET ------ int ChaletId PK int Berth ACCOUNT ------- int AccountId PK int ChaletId FK The chalets start off un-referenced. When a user buys a chalet the code needs to find an unreferenced chalet and assign it to a newly created account. I think that...

SubSonic column named 'Key'

Hi! I have a column named 'Key' in a MySQL database. Seems that the repo.Find<Class>(x=>x.Key.StartsWith("BLAH")); generates the SQL code WHERE Key LIKE 'BLAH%' instead of the correct one like WHERE `Key` LIKE 'BLAH%' How can I force the later behaviour (is it a bug in SubSonic?) Cheers, Tomasz ...

mysql left join question

I've got two tables, one holds reservations for a room, and the other is a "mid" table to hold the dates that the room is reserved on (since a reservation could have multiple non-sequential dates). It looks something like: Res_table: id, room_id, owner_id Res_table_mid: id, res_id, date The res_id column in the res_table_mid referen...

SQL Syntax Error whilst creating a table

Hi, I have recently started learning SQL, I can't determine what is causing my syntax error (see below): CREATE TABLE Users( user_id smallint not null auto_increment, username varchar(50) unique, password varchar(41), dob date, session_id varchar not null, cookie varchar not null, PRIMARY KEY(user_id), C...

MySql 2 rows subquery

I got mysql query like this: SELECT name, (SELECT timePing FROM TerminalPings WHERE terminalsId = Terminals.id ORDER BY timePing DESC LIMIT 1) as timePing FROM Terminals` It works good, but i need to select top 2 timePing's from TerminalPings, and name them like timePing1 and timePing2. What is the best way to do ...

Query error while working with PL/pgSQL arrays

I have this function: create or replace function insert_aereo( aereo_type[] ) returns text as $$ begin return 'ok'; end $$ language plpgsql; and this is the parameter type that I created: create type aereo_type as (codice int, modello varchar, marca varchar); Then I call my function: select insert_aereo('{123, "ciao", "pippo"}'...

Relational database structure for Texas Hold'em poker data

Hello, I am planning a relational database to store poker game data (like what would be included in the hand histories). I would like help figuring out how to design the associations. It seems like there should be 4 models: Game, Hand, Player, and Action (single action of a given player, like raise, fold, call). Let me lay out what I h...

Array multi insert uname and email check

I have an array I want to check whether the username and Email values exists in database, if it does not exist insert the username and email values in the database. Using Multi insert since there are around 80000 inserts to be preformed. $arr1 = Array ( [0] => Array ( [Username] => uname1, [Email] => email1 ) [1] => Arr...

How do I find records matching attribute with 1+ values in ActiveRecord/SQL?

How do I find records matching attribute with 1+ values in ActiveRecord/SQL? Examples would be something like: Post.find_by_type("Post and/or ChildPost") Post.find(:type => "Post and/or ChildPost") How can I do this? The number of values will be no more than 10 I'd say. ...

Optimizing MySQL queries / database structure

I have in my MySQL database these two tables: CREATE TABLE IF NOT EXISTS `articles` ( `id` bigint(20) NOT NULL, `url` varchar(255) collate utf8_bin NOT NULL, `img` varchar(255) collate utf8_bin NOT NULL, `name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL, `url_key` varchar(255) character set utf8 collate u...

Need help with some Oracle SQL Queries

Here is the question posed by a professor: Find the minimum and maximum length of movies playing in each city. And here is how I have my tables structured: CREATE TABLE Theatres ( Name varchar2(50) not null, City varchar2(50) not null, State varchar2(50) not null, Zip number not null, Phone varchar2(50) not null, PRIMARY KEY (Name) ); ...

Follow-Up Help with a SQL Query

I asked something similar a few days ago, here is my issue. My professor has phrased the following question: Find the average ratings of all movies playing at each theatre. Display the theatre name and the computed rating. Order the results ascending by rating. Here is how my tables are structured: CREATE TABLE Theatres ( Name var...

DB Concept needed for record editing and rollback ability

I love the way SO retains the edits to Q/A's and allows us to roll back if need be. I'm looking for a DB structure concept on how to implement something similar to this. Can anyone give any insights? My current solution is to have two tables like so... Table1 ID | Date | UserID . Table2 ID | Table1ID | UserID |...

ORACLE SQL Statement

So I need help modifying my query to not just include one movie from each city, but all movies from each individual city. Here is my professor's question: Find the average rating of all movies playing in each city. Display the city name and the computed rating. Order the results descending by rating. Here is my definition for each tabl...

What database to use?

I'm new to databases, but I think I finally have a situation where flat files won't work. I'm writing a program to analyze the outcomes of multiplayer games, where each game could have any number of players grouped into any number of teams. I want to allow players can win, tie, or leave partway through the game (and win/lose based on te...

Oracle SQL Question

So I've been doing this all night - can't quite understand my homework, and sadly my professor is unavailable on the weekend. Here it goes: Find the titles of the newest movies shown in each city. Display the city name and the newest movie title ordered by city name and movie title. Here are my table declares (and thank you to EVERYONE...

SQL Query Group By Datetime problem?

I have this SQL query: SELECT DISTINCT [BatchCode] ,SUM([Quantity]) as 'Created' ,[TotalQuantity] ,[Status] ,[Destination] ,[DateCreated] ,[CreatedBy] FROM [FGIS].[dbo].[DropshipPackinglist] GROUP BY BatchCode, TotalQuantity, Status, Destination, CreatedBy, ModifiedBy...