sql

Increase Query Speed in PostgreSQL

Hello, First time posting here, but an avid reader. I am experiancing slow query times on my database (all tested locally thus far) and not sure how to go about it. The database itself has 44 tables and some of them tables have over 1 Million records (mainly the movies, actresses and actors tables). The table is made via JMDB using the ...

IS NULL vs = NULL in where clause + SQL Server

Hello How to check a value IS NULL [or] = @param (where @param is null) Ex: Select column1 from Table1 where column2 IS NULL => works fine If I want to replace comparing value (IS NULL) with @param. How can this be done Select column1 from Table1 where column2 = @param => this works fine until @param got some value in it and if is...

PostgreSQL and PHP forms

Ok I have a PostgreSQL server with a Database titled brittains_db that I only have PuTTY access to. I can also upload via FTP to the web server which has access to PostgreSQL and the Database somehow... I have made a SQL file named logins.sql CREATE TABLE logins( userName VARCHAR(25) NOT NULL PRIMARY KEY, password VARCHAR(25) N...

Using CASE Statements in LEFT OUTER JOIN in SQL

I've got a scenario where I want to switch on two different tables in an outer join. It goes something like this:- select mytable.id, yourtable.id from mytable left outer join (case when mytable.id = 2 then table2 yourtable on table1.id = table2.id ...

How can I make an SQL statement that finds unassociated records?

I have two tables as follows: tblCountry (countryID, countryCode) tblProjectCountry(ProjectID, countryID) The tblCountry table is a list of all countries with their codes and the tblProjectCountry table associates certain countries with certain projects. I need an SQL statement that gives me a list of the countries with their country...

predefined function or method available to get second highest salary from an employee table?

is there any predefined function or method available to get second highest salary from an employee table ...

About global.asax and the events there

So what i'm trying to understand is the whole global.asax events. I doing a simple counter that records website visits. I am using MSSQL. Basicly i have two ints. totalNumberOfUsers - The total visist from begining. currentNumberOfUsers - Total of users viewing the site at the moment. So the way i understand global.asax events is that...

Select columns from join table only without requiring a join

Given these tables: create table Orders ( Id INT IDENTITY NOT NULL, primary key (Id) ) create table Items ( Id INT IDENTITY NOT NULL, primary key (Id) ) create table OrdersItems ( OrderId INT not null, ItemId INT not null, primary key (OrderId, ItemId) ) Is it possible to use HQL/criteria API to contruct a query...

Ways to update a dependent table in the same MySQL transaction?

I need to update two tables inside a single transaction. The individual queries look something like this: 1. INSERT INTO t1 (col1, col2) VALUES (val1, val2) ON DUPLICATE KEY UPDATE col2 = val2; If the above query causes an insert then I need to run the following statement on the second table: 2. INSERT INTO t2 (col1, c...

Why does this query only select a single row?

SELECT * FROM tbl_houses WHERE (SELECT HousesList FROM tbl_lists WHERE tbl_lists.ID = '123') LIKE CONCAT('% ', tbl_houses.ID, '#') It only selects the row from tbl_houses of the last occuring tbl_houses.ID inside tbl_lists.HousesList I need it to select all the rows where any ID from tbl_houses exists within tbl_lists.HousesL...

Is a JOIN more/less efficient than EXISTS IN when no data is needed from the second table?

I need to look up all households with orders. I don't care about the data of the order at all, just that it exists. (Using SQL Server) Is it more efficient to say something like this: SELECT HouseholdID, LastName, FirstName, Phone FROM Households INNER JOIN Orders ON Orders.HouseholdID = Households.HouseholdID or this: SELECT Ho...

Cakephp, i18n, SQL Error, Not unique table/alias

I get the following SQL error: SQL Error: 1066: Not unique table/alias: 'I18n__name' when doing a simple find query. Any ideas on possible situations that may have caused this?? I'm using a bindModel method to retrieve the data is that related? This is my code: $this->Project->bindModel(array( ...

Datatype to save excel file in sql server?

Hi, I have a table in which there are two columns : 1. import type, 2. Excel import template. The second column - "Excel import template" should store the whole excel file. How would I save excel file in databse...can I use binary datatype column, convert excel file to bytes and save the same ? Thanks in advance ! ...

SQl to list rows if not in another table

I have the following query which have 1000 rows select staffdiscountstartdate,datediff(day,groupstartdate,staffdiscountstartdate), EmployeeID from tblEmployees where GroupStartDate < '20100301' and StaffDiscountStartDate > '20100301' and datediff(day,groupstartdate,staffdiscountstartdate)>1 order by staffdiscountstartdate desc i ...

Drop a DB2 view if it exists...

Why doesn't this work in IBM Data Studio (Eclipse): IF EXISTS (SELECT 1 FROM SYSIBM.SYSVIEWS WHERE NAME = 'MYVIEW' AND CREATOR = 'MYSCHEMA') THEN DROP VIEW MYSCHEMA.MYVIEW; END IF; I have a feeling it has to do with statement terminators (;) but I can't find a syntax that works. Another similar question at http://stackoverflow.co...

Transfer records from one MySQL DB to another.

How can I transfer specific rows from one MySQL DB on one server to another in a completely different server. Also the schemas don't necessarily have to be the same. For example on server "A" I could have a Users table with 2 columns and on Server "B" have a Users table with 4 columns. Thanks. ...

SQL simple query

I have the following table, Persons_Companies, that shows a relation between persons and companies knowns by these persons: PersonID | CompanyID 1 1 2 1 2 2 3 2 4 2 Imagining that company 1 = "Google" and company 2 is = "Microsoft", I would like to know the ...

EntityFramework Procedure or function '' expects parameter '', which was not supplied.

I apologise for asking just a basic question, however I cannot find the cause of this error. I am using Entity Framework to execute a Stored Procedure, and I am passing in four parameters, however the SQL Database seems to reject them. Can anyone point me in the right direction? My code: ObjectResult<SearchDirectoryItem> resultList = ...

Use analytic functions to group a set of records when timestamps in rows is less or equal than a value

Hi all, I have a table in an Oracle Database that has, among others, a DATE column that is loaded with the insertion timestamp of each row. I need to use existing data in such table to analyze the correlation between some events, so that with data like this: COL_1 COL_2 TS A 1 Mon 15, February 20...

Detecting changes between rows with same ID

I have a table containing some names and their associated ID, along with a snapshot: snapshot, systemid, name[, some, other, columns] I need to identify all the unique names that a systemid has had across all snapshots, but only where there has been at least once change. For example, with the data: 'DR1', 0, 'MOUSE_SPEED' 'DR1', 1, ...