Apologies for the less than ideal title; had a hard time coming up with something. Three entities: Products, Clients and Client_Product_Authorization. A Client has access to many Products. A Product is accessed by many Clients. A Client_Product_Authorization authorizes a Client to access a Product. We have 100,000+ Products (virtual...
Hello! The functions "REGEX()" and "TRIM()" in this script don't work as I would expect. The REGEX-function returns always true and the TRIM-function returns the "trim_char", not the trimmed string. (When I write the TRIM-function with FROM instead the "," I get an error message.) #!/usr/bin/perl use warnings; use strict; use 5.010; us...
In general what makes an SQL query optimiser decide between a nested loop and a hash join. ...
I can't seem to get reliable results from the query against a sqlite database using a datetime string as a comparison as so: select * from table_1 where mydate >= '1/1/2009' and mydate <= '5/5/2009' how should I handle datetime comparisons to sqlite? update: field mydate is a DateTime datatype Solution: following the datetime ...
Hello... So I'm working on a PHP project that needs to allow the user to import CSV files into MySQL. The CSV files contain a "column" with a unique ID... there are duplicates. Here is where the tricky part comes in... The duplicates need to go into a separate table and not into the main table. I have written code to do this but there...
I want to create a query to list of all user defined stored procedures, excluding the ones that are system stored procedures, considering that: Checking the name like "sp_" doesn't work because there are user stored procedures that start with "sp_". Checking the property is_ms_shipped doesn't work because there are system stored proced...
I want to update the order so that GALLERY goes before PRODUCTS. What should be the query to do this? Sorry for my English. ...
I'm involved is a project to migrate a project from Oracle to MySQL. In Oracle I have the ability to create a SQL script that references or inlcudes other external SQL script files when the batch is run via command line. I Have a script called CreateAllTables.sql that looks like this internally: @tables\Site.sql @tables\Language.sql @ta...
I was just wondering if that default READ COMMITTED isolation level somehow makes the select statement act different inside of a transaction than one that is not in a transaction, anybody knows ? I am using MSSQL ...
I want to query a set of tables based on the column name, type, etc. Basically without knowing the name of a table I want to query the database for all tables capable of storing the data into. I have this query but I'm not sure if it's very efficient. I was wondering if there is a better way. SELECT O.TABLE_NAME FROM INFORMATION_SCHEM...
I need to display what the table contains from the freshest data to the oldest. Something like this doesn't work: SELECT * FROM table ORDER BY DESC;. I know its becouse after ORDER BY should be name of the column. But I want to just reverse normal order (by normal I mean from the oldest to the freshest data). How to do this? ...
I am trying to speed up a long running query that I have (takes about 10 minutes to run...). In order to track down what part of the query is costing me the most time I included the Actual Execution Plan when I ran it and found a particular section that was taking up 55% (screen shot below) This didn't quite seem right to me so I add...
MS Access: How to replace blank (null ) values with 0 for all records? I guess it has to be done using SQL. I can use Find and Replace to replace 0 with blank, but not the other way around (won't "find" a blank, even if I enter [Ctrl-Spacebar] which inserts a space. So I guess I need to do SQL where I find null values for MyField, then...
Hi I'm having some problem with logic of the comparison of some periods. I have a table in my database that looks like this: Id|startDate |amount of weeks| -------------------------------- A1|2010-01-04 | 3 B3|2010-01-11 | 2 all the startDates start on the same day of the week (Monday) now I need to write an sql where I have 2 para...
I am new to designing my own database. My project is orders/products/inventory type data, using SQL Server 2005. During my reading I came across several customer/order type of examples, and I decided to use linking-table (sometimes here called junction, or join table) for my Order_Details since I had many-to-many relationships between OR...
I'm using Mysql 5.0 and am a bit new to indexes. Which of the following queries can be helped by indexing and which index should I create? (Don't assume either table to have unique values. This isn't homework, its just some examples I made up to try and get my head around indexing.) Query1: Select a.*, b.* From a Left Join b on b.type...
I'd like to create an SQL table that has no more than n rows of data. When a new row is inserted, I'd like the oldest row removed to make space for the new one. Is there a typical way of handling this within SQLite? Should manage it with some outside (third-party) code? ...
I am trying to get a results that will show Uniuque "Reasons", the number of them there are, and the percentage of the total that they are. so far I have SELECT DISTINCT Reason, COUNT(Reason) AS Number, CAST(COUNT(Reason) AS float) / CAST(COUNT(*) AS float) AS percentage FROM DeletedClients However as I have discovered...
I have a MySQL table with an index covering two fields. If I insert new rows into the table, will my index still work? ...
Hey i am looking for a lib that can take the class below and generate these two tables with a reference/foreign key and allow me to tweak the default logic. Like below, unless i specify otherwise when i do sqlClass.Insert(imglist) it will not create another entry for user_id but try to find an existing one in the database. class ima...