sql alias field
in SQL, supposes I need to refer to an aliased field in the having clause but the alias has quotes, how do I do that? select (select...) as '005' group by ... having '005'>0 ...
sql
Inner join on regexes
I have an inner join on regular expressions - it is very slow. Is there any easy way to speed this up? I am using postgres. FROM A inner join B ON trim(lower(replace(replace(replace(B.enginequery,',',' '),'"',' '),'+',' '))) = trim(lower(A.keyphrase)) OR trim(lower(replace(replace(replace(B.enginequery,',',' '),'"',' ')...
Calculate running total in SQLite table using triggers
How can I create a SQLite Trigger to calculate running totals on the "Actual" table? The following SQL code should update the AccountBalances table so that the Balance column counts from 1, 2, 3, ... rowcount. However, the trigger only updates the 2nd row, even when I turned on recursive_triggers. The result below is row 1 = 1, row 2 = 2...
SQL Server 2005 + Enforce Foreign Key Constraint + Cacade Delete
I am using SQL Server 2005 and I have to relationships going into one table. I had to turn off " Enforce Foreign Key Constraints" because I have 2 relationships going into the same table. However I want to put cascade delete on. I thought if I have cascade delete on both of these relationships and if I say deleted something from on of...
Limit results from joined table to one row
Here is a simplified table structure: TABLE products ( product_id INT (primary key, auto_increment), category_id INT, product_title VARCHAR, etc ); TABLE product_photos ( product_photo_id (primary key, auto_increment), product_id INT, photo_href VARCHAR, photo_order INT ); A product can have multiple photos, the first product...
I am looking for a radio advertising scheduling algorithm / example / experience
Hi Everyone, Tried doing a bit of research on the following with no luck. Thought I'd ask here in case someone has come across it before. I help a volunteer-run radio station with their technology needs. One of the main things that have come up is they would like to schedule their advertising programmatically. There are a lot of nea...
CSV to MySQL conversion and import
Hi all, Im working on a large project, havent had to do what I need help with before. I have a csv file, it contains a large amount of data, namely all of the cities, towns, suburbs in Australia. I need to convert the csv file to sql for mysql, and then import it into the database. What would be the best way to achieve this? Thanks al...
No Exact match when using LIKE in SQL statement
SELECT bp.*,r.rating,COUNT(r.review_for),bp.business_name,bp.profile_member FROM ibf_business_reviews r LEFT JOIN ibf_business_profiles bp ON ( r.review_for=bp.profile_member ) WHERE bp.sub_category LIKE '%{$id},%'{$location_sql} GROUP BY r.review_for HAVING COUNT(r.review_for) >=1 ORDER BY r.date_posted DESC LIMIT 0,2...
SQL query determine stopped time in a rnge
hi i have to determine stopped time of an vehicle that send back to server its status data every 30 second and this data store in a table of database, the fields of a status record consist of (vehicleID,RecieveDate,RecieveTime,Speed,Location). now what i want to do is, determine each suspention time at the point that vehicle spee...
Query in sql involving joins of two table
EDIT 2 I have two tables reports and holidays. reports: (username varchar(30),activity varchar(30),hours int(3),report_date date) holidays: (holiday_name varchar(30), holiday_date date) select * from reports gives +----------+-----------+---------+------------+ | username | activity | hours | date | +----------+--------...
Can't use MySQL extract() function in the WHERE clause.
I've run the following query: UPDATE main_table, reference_table SET main_table.calc_column = (CASE WHEN main_table.incr = "6AM" THEN reference_table.col1+reference_table.col2+... WHEN main_table.incr = "12AM" THEN reference_table.col7+reference_table.col8+... WHEN main_table.incr = "...
Help doing a dynamic sort?
I have a notifications table which contains different types of notifications for different events. Inside the table is a notifications_type:string column that contains the type of notification, i.e. "foo" or "bar" or "oof" I want the user to be able to select what notifications they want to display, so there are checkboxes below the res...
Question about DBD::CSB Statement-Functions
From the SQL::Statement::Functions documentation: Function syntax When using SQL::Statement/SQL::Parser directly to parse SQL, functions (either built-in or user-defined) may occur anywhere in a SQL statement that values, column names, table names, or predicates may occur. When using the modules through a DBD or in any other con...
how to get last weeek dates from today to last 7 days date..
i have a table with column: Registereddate orgid 2010-06-05 10:16:00 1 2010-06-05 10:10:00 2 2010-06-04 22:31:00 3 ... . . ..... . . . i need to get only last weeek dates and orgid from today to last 7 days date.. ...
adding checkbox to a combobox
I want to add check boxes inside a combo box and then use the values of the checked boxes in my sql query. Also I need to append the checked values to an array and then use the elements of that array in my sql query. How can I proceed? ...
Best way to fetch last 4 rows from a result set using mysql
Can any one please let me know, that, i need to fetch last 4 rows from a result-set using mysql. The result-set returns totally 6 records. but, i need the records to be fetch from last4...i.e, Record-3 Record-4 Record-5 Record-6 ...
how do I get the 2 most recent records
I have a table similar to the example shown below. I would like to be able to select the two most recent entrys for each accountNo. I am using Microsoft SQL 2000. Thank you for any help that you can provide. AccountNo, DateOfOrder, OrderID ----------------------------------------- 123, March 1 2010, 1 222, March ...
php import larg table to phpmyadmin database
hi, I am so worry :( I dropped one of the tables from the database accidentally. fortunately, I have back-up. (I have used the "Auto backup for mysql") The back-up of the table is stored as .txt file (56 Megabytes) on my PC. I tried to import it by PhpMyAdmin and the import failed because the file is too large to import. then I up...
How Can i Create This Complicated Query ?
Hi, I have 3 tables: projects, skills and project_skills. In projects table i hold project's general data. Second table skills i hold skill id and skill name also i have projects_skills table which is hold project's skill relationships. Here is scheme of tables: CREATE TABLE IF NOT EXISTS `project_skills` ( `project_id` int(11) NOT NU...
SQL - Dervied Foreign Key - Possible?
I'm just curious if this is possible, specifically in SQL CE (Express) with support in .NET's Entity Framework: Table1 (primary) -nvarchar(2000) url -... Table2 (with foreign key) -nvarchar(2000) domain -... foreign key on Table2.domain references Table1.url such that Table.url contains Table2.domain e.g. Table1: http://www.google....