I have three tables to define users: USER: user_id (int), username (varchar) USER_METADATA_FIELD: user_metadata_field_id (int), field_name (varchar) USER_METADATA: user_metadata_field_id (int), user_id (int), field_value (varchar) I'd like to create a middle tier user that has certain access to other users within the application. To d...
I have a query that executes fine in TOAD, but when bringing back the results with either an ADO.Net DataReader or DataAdapter/DataSet, it's not recognizing a column in the select statement. See the query below... the column in question has its data coming from a dynamic table or sub-select or whatever you call it. I don't see why this...
I have a table, let's say it stores staff names (this isn't the exact situation, but the concept is the same), and the primary key is set to an autonumber. When adding a a new member of staff I want to check to see if that name exists in the database before it is added, and maybe give an error if it already exists. How can I do this from...
I am building a database system and having trouble with the design of one of my tables. In this system there is a users table, an object table, an item table and cost table. A unique record in the cost table is determine by the user, object, item and year. However, there can be multiple records that have the same year if the item is di...
I have a table with inventory records and another table with documents describing each inventory records, something like: inventory (t1) t1id t1c1 1 desc1 2 desc2 3 desc3 documents (t2) t2id t2c1 t1id 1 doc1 1 2 doc2 1 3 doc3 2 4 doc4 3 so i try this que...
Given the following sample of XML and the select statement that shreds the xml into a relation, what I need is the second column of the select to be the ordinal of the category (ie 1 for the directions and 2 for the colours in this case). Note: The literal value 'rank()' in the select is left a placeholder. I was poking around with usin...
Imagine I have 2 tables in my database, with the same schema (columns, types, etc). Can Linq-To-Sql treat them as Table<AClass> using the same class for both of them? How to do it? ...
I have a SQLClient.DataSet in VB.NET, and I want to insert the entire thing into a SQL Server table without having to do the following: For Each dr as Datarow in MyDataset Dim sc As New SqlCommand("INSERT INTO MyNewTable " & _ "VALUES (@column1, @column2)", MyDBConnection) sc.Parameters.AddWithValue("@col...
I'm new to working with analytic functions. DEPT EMP SALARY ---- ----- ------ 10 MARY 100000 10 JOHN 200000 10 SCOTT 300000 20 BOB 100000 20 BETTY 200000 30 ALAN 100000 30 TOM 200000 30 JEFF 300000 I want the department and employee with minimum salary. Results should look like: DEPT EMP SALARY ---- --...
I'm new to Oracle (I've been using MySQL mainly until now) so this might be a dumb question. But I have created this table (names are not english but ignore that, that is not important): CREATE TABLE Auta ( id_auto NUMBER(5) UNIQUE NOT NULL, typ CHAR(10), specifikacia_typu CHAR(15), SPZ CHAR(8), farba CHAR(20), datum_vyroby DATE, pocet_...
Regarding MySql, is there a character set to support all or the vast majority of languages? ...
So I came across an instance where a stored procedure which handled the Updates on a specific table was doing nothing because the record didn't exist [duh]. So what I decided to do was change the stored procedure to: UPDATE Table SET col1 = @var1 WHERE Type = @type AND UserId = @userId IF @@ROWCOUNT = 0 BEGIN INSER...
Making an automated call list to be generated based on certain search criteria that pulls customers names and phone numbers. There are 4 tables: Customer, Phone_Numbers, Sales_Header, Sales_Detail. The query is as follows: SELECT CONCAT(customer.First_Name, ‘’, customer.Last_Name), Phone_Numbers.Number, Customer.ID FR...
which is ironic on two counts, 1) because concat(charfield, doublefield) works (it doesn't mind when one of the fields to be concatenated is numeric) and 2) because the mysql reference shows this: CONCAT(str1,str2,...) as the prototype for CONCAT and for FORMAT this: "FORMAT(X,D) Formats the number X to a format like '#,###,###.##', rou...
Hi, I've the following sql query: SQL = "SELECT * FROM Product WHERE ProductCategoryId = " & Request.QueryString("CategoryId") This query tells to get all the products from ONE category. I want the ability so products can be from some categories, and not from one category only. So, I changed the [Product].ProductCategoryId field t...
I have a number of really large files with many insert statements (18.7 million in the largest one). At the mysql> prompt if I do a source file.sql or a ./file.sql everything works good, things get inserted, but there is output for every run statement which ways: Query OK, 1 row affected (0.00 sec). When run this way these inserts can t...
Any idea why this fails in MySQL 5.1: SET @LOAD_TIME = UNIX_TIMESTAMP(); SET @OUTFILE = CONCAT(CONCAT('/tmp/outfile_', @LOAD_TIME), '.sql'); SELECT * FROM `tableA` INTO OUTFILE @OUTFILE; Is it a limitation of MySQL's SELECT or am I missing something here? ...
What is the time complexity of a function such as count, sum, avg or any other of the built in "math"-functions in mysql, sql server, oracle and others? One would think that calling sum(myColumn) would be Linear? But count(1) isn't, how come and what are the Real time-complexity? In a perfect world I would want sum, avg and count to b...
I have a stored proc that calls several store procs, each of which insert dummy data into a single table each. It works fine except that for each loop in the cursor a single row of results is dispayed - just showing ClubcardId = 2, ClubcardId = 3 etc. I have used the SET NOCOUNT ON but this doesn't seem to help. I'm looking for this sto...
Alright, let's say I have these two tables: items with columns id, stuff item_properties with columns item_id, prop_id Now I want to execute a query like SELECT stuff FROM items WHERE EXISTS(SELECT * FROM item_properties WHERE prop_id = 123 AND item_id = items.id) AND EXISTS(SELECT * FROM item_properties WHERE prop_id = 456 ...