sql

SQL query: finding a gap in a primary key

Hi there How can I write a single select statement that does the following: I have an integer column in my table and i want to find the minimum available (non-used) value in that column where the value is below 1000 and also where the value does not exist in TableB Column1 Thanks ...

[beginner question] get data form 'dictionary' table

I have two tables in database: A: [ ID, name, SECRET_KEY] examplary rows: ID1, John, X ID2, Jane, Y and second ( 'dictionary') B: [SECRET_KEY, explanation] like: X, Driver Y, Doctor i want to list all records from table A but with secret_key translated to it's explanation ( based on relation from table B). expected result: ...

sql server trigger

Hi, I would like to write trigger that after inserting record to table Person I would like to create new record in table Car with PersonID = newly inserter ID from table Person: Person: ID Name Car: ID PersonID //FK to Person Name thanks for any hint, bye ...

How do you order by a search phrase found in the beginning of a word and only afterwards the middle using SQL.

Let's say I have table [users] with the field [Name] and I've written a query in my application to search by name. The names in the database are: Lala Ally My SQL query is: SELECT * FROM users WHERE [Name] LIKE '%al%' where 'al' is my search term. How would I sort the results by search term found in the beginning i.e. '%al' and o...

SQL: Add all values from different rows

Hi, I have two SQL queries I want to combine into one. The first one selects all the IDs of the rows I need to add in the second one: SELECT t.mp_id FROM t_mp AS t JOIN t_mp_og USING (mp_id) WHERE og_id = 2928 AND t.description = 'Energy' The second one should add together the values from the rows returned by the first query. Up unti...

H2 database: Information about primary key in INFORMATION_SCHEMA

I create the following table in H2: CREATE TABLE TEST (ID BIGINT NOT NULL PRIMARY KEY) Then I look into INFORMATION_SCHEMA.TABLES table: SELECT SQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TEST' Result: CREATE CACHED TABLE TEST( ID BIGINT NOT NULL ) Then I look into INFORMATION_SCHEMA.CONSTRAINTS table: SELECT SQ...

T-SQL Reverse Pivot on every character of a string

We have a table like below in an sql server 2005 db: event_id staff_id weeks 1 1 NNNYYYYNNYYY 1 2 YYYNNNYYYNNN 2 1 YYYYYYYYNYYY This is from a piece of timetabling software and is basically saying which staff members are assigned to an event (register) and the set of weeks the...

insert xls/csv file into access 2007 table using vba

I have several excel files carrying 90 columns and about 500 rows, each file has a header of 4 rows, and I need to insert them into one access table. I am able to do the import by using the for...loop to loop all the excel files and all of the rows inside the file, but this is too slow. I have used mysql in R and it can be done easily ...

Whats a simple/quick file-upload script programmed in PHP?

I am making a website which gives the user the privilege of uploading a text document to the server. The server then should take the file and store it in the ftp server when it is uploaded and the SQL database should hold the data associated with the file. ...

Exchange 2003 as a SSIS data source

Hi all, I am new to stackoverflow, and this is my first question: I would like to create a SSIS task on SQL 2005 server that queries a specific email account on an Exchange 2003 server, and creates a report. The report currently contains the amount of emails received, average time of reply, and emails with reply time that have gone ov...

ASP.NET query substring

I've got this field in my database year_start_1 and it is an integer field and an example of an ouput is 20100827 I'm trying to create a substring to create year, week, day and change the format to be 27/08/2010 Here's what i'm trying Dim query as String = "Select * from openquery (devbook, 'SELECT cast(year_start_1 as varchar(8)) as ...

Union 2 table that have not a same attribute in SQL

I'm doing a SQL assignment and I found that one question ask me to union 2 table together but this two table isn't have a same attribute. The question will show below. List the union of customers and employees. For the customers, list customer name (first and last) and contact (CustStreet, CustCity, CusState). For the employees, list em...

What kind of code is this?

I have some coded strings in my database which reference the categories of my site. They look like this a:1:{i:0;s:12:"TVRFNE1EYz0=";} I'm wondering what they are so I can create them all to use in a CSV file as creating them on my site is very time consuming. Thanks Oh actually I really don't think this is possible. It happens whe...

Multiple field report, with custom counts in MySql

Hey all, I am using a system I didn't create. The system has 3 main tables: users, courses, and usergroups. I am using an extra table called coursehits. It's a MySQL DB, 5.0. There aren't any relationships in the DB, so users are assigned to courses by simply adding an entry to usergroups (course_id and user_id) from the courses and u...

Select per month

I've got the following table: purchases(id, item, user, price, time); The time field is a timestamp. I need a query that would return one row per month and that row would contain the sum of price for each item in that month. ...

Wordpress post loop have posts fatal error?

I made a custom post loop query based on data from another plugin. I get this error: Fatal error: Call to a member function have_posts() on a non-object Here is the code I have made: ...

Can I have a materialized view refresh on commit only for two of three tables in its select statement?

I would like to use a materialized view that refreshes ON COMMIT. My backing view joins two tables in the local database and one table in a remote database via DB Link. How can I have the view refresh only when changes are made to one of the two local tables? Are there any other ways to solve this problem? Can I have the materialized...

How to get created stored procedures in last 2 weeks?

Is there a way I can detect when was a stored procedure created/modified, so I can get a list of what has been altered in the last 2 weeks? ...

SQL: Select MIN value that dosnt already exist

Hi there, In TableA I have an int column. Is it possible using only a select statement to select the minimum value in the column that DOES NOT EXIST and is greater then 0 For example if the col has the values 1,2,9 the select statement will return 3. If the col has 9,10,11 it will return 1 I can achieve this using a temp table or us...

Compare when value could be both NULL or text

Now I know you can't directly compare NULL to anything (as null is unknown) so how would I achieve the following: select * from Material as m where MtrlCode = 826 and Exposlimit <> 'compareMe' Where Exposlimit MAY be NULL or it may not be. 'compareMe' may also be NULL. Therefore how do I compare the two? Bo...