sql

MySql - Inserting multiple rows with a joined subquery?

This query will return a list of project IDs that represent forum threads: SELECT id FROM `proj_objects` WHERE type='fthread'; This query will subscribe a user (whose ID in the users table is '37') to the forum thread with an ID of '122': INSERT INTO `subscrips` VALUES ( 37, 122 ) ; I'd like to insert multiple rows that will subsc...

Example Use of MySQL Indexes?

I was reading this question about the difference between these 4: http://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-mysql However, it is all very abstract and vague to me after reading it. Maybe it would help me understand it more concretely if I had some examples of when I would use it. For ex...

Joining 3 tables into 1 - SQL

I'm trying to LEFT OUTER JOIN two tables and then INNER JOIN another table together in Access 2007. SELECT RestaurantName, StreetAddress, City, State, Zip, RestaurantWebsite, MenuLink, RestaurantTimes, PhoneNumber, PictureTi...

Access top n in group

I have a table where I need to get the top n highest amount items for each Category. Category Item InventoryCount ------- ----- ------------- Beverage milk 3 Beverage water 2 Beverage beer 9 Utensil fork 7 Utensil spoon 2 Utensil knife 1 Utensil spork 4 My desired output is the highest Inventory...

Efficiently pulling thousands of records with SQL Server?

This is kind of a part 2 to this question: http://stackoverflow.com/questions/3319842/select-rows-and-update-same-rows-for-locking I have a table in my database that hold millions of records. I have created a stored procedure to pull X amount of records and mark them as being locked so when my application calls the next set of X amount ...

SQLAlchemy - ObjectDeletedError: Instance '<Class at...>' has been deleted. Help.

I'm having some issues with deleting rows from a database and then adding new ones. Here's the code: for positionid in form_result['responsibilities']: inputdata = form_result['responsibilities'][positionid] self.__deleterow(dbmyaccount.Responsibilities, session['authed']['userid']) for resp in (i.strip() for i in inputdata...

How to get difference between 2 columns

I have a query that is producing something like this: StartTimestamp | EndTimestamp ================================ 100 | 450 -------------------------------- 150 | 500 I'd like the result to also include the difference between EndTimestamp and StartTimestamp: StartTimestamp | EndTimestamp | Difference ...

Subsequent queries depends on result of initial query: how to?

Say I have 3 queries. Query 1 returns a piece of information that Query 2 and Query 3 needs. Is there a way for Query 2 and Query 3 to access this piece of information from the result of Query 1? Right now, I have Query 1 executing twice: once in Query 2 and once in Query 3. This doesn't seem efficient to me. Is there a better way in M...

Ensuring uniqueness of additions to MySQL table using PHP

I'm trying to create a page that tracks some basic user statistics in a database. I'm starting small, by trying to keep track of how many people come using what User Agent, but I've come across a stumbling block. How can I check the User Agent being added to the table to see if it is already there or not? ...

SQL Server Reporting Service

Hi everybody. Recently I saw a presentation in which the reporting part of the software had a nice feature. When we hovered the mouse over the charts in the report, datailed information regarding that part of the chart appeared as a tooltip. Is there such a feature in SQL Server reporing services 2005? If not, how can I add it? Also ...

Text keyword search in 200 or so char text field on database

Here's the situation, hugely grateful for any input anyone may have: I have a table of about 100m rows, with (among several other fields), a text field that we can call 'product'. The product field is about 200 chars long, and contains details of, yes, products. I want to give users the ability to search through these items by entering...

How to get started with sql and php?

I am just getting started in php and sql, I was wonder if how to link sql database to on my php code, I know how to create databases and i know a little about php, but i don't know how to link the databases to my code. i am using a database on my local server that i set up and i have already created a database, i tried writing a php scri...

Select Top (some number) with UNION ALL and ORDER BY

I am trying to pull back the top five instances of certain records loaded into a table. It is important to note that I am trying to get my results out of the same table and I think there is a problem that I can't quite figure out related to the fact that this is one table. Here is the example of my query: Select * From ( Select Top 5 ...

Can [My]SQL combine multiple overlapping composite indexes?

Basically, if I have a table with composite indices (colA, colB, colC) and (colB, colD, colE), is MySQL able to take advantage of their overlap on colB to combine them and speed up a query involving colA, colB and colD, even if there is no single index covering these three particular columns? I tried using EXPLAIN on a test case like th...

SQL optimization/tweaking

I have this SQL Server query SELECT count(distinct [IP]) as GlobalUniqueIPcount, --RangeUniqueIPcount (SELECT count(distinct [IP]) FROM [tblSequence] WHERE SiteID = @siteID AND ([Timestamp] > DATEADD(dd, -@days, (LEFT(GETDATE(),12))))) as RangeUniqueIPcount, --RangeUrlUniqueIPcount (SELECT count(distinct [IP]) FROM [tbl...

Best Practice append, Insert into Table . Queries

Hi trying to acknowledge the best practice to update Insert data from A table A to Table B . I have ADOConnection1 Table Clientes and ADOConnection2 Table Q_Clientes Now i knead to update ADOConnection1 Table Clientes \Q_Clientes has the same similar to Clientes only it´s in another Locacion i can call clientes.FieldByName('Localização'...

looping through multiple arrays and inserting into SQL

Hi, I have a form that once submitted some of its result are stored in arrays, example: (The form has multiple lines with the same input names) <select name="product[]"> once submitted goes into $_GET['product'] if I do: // Product ID's foreach($_GET['product'] as $name => $prodvalue) { print "$name : $prodvalue<br>"; } the follo...

sql query returns error when using limit

hi, why does this query work? how am i supposed to write it? select * from tbl_content order by year desc where visible = '1' limit 0,30; ...

How stored procedure is processed by Sql Server and .Net

I have been using stored procedure for more than 1.5 years. But I never thought how data is retrieved from UI or within another stored procedure. When I writes a simple stored procedure. eg. CREATE PROCEDURE sp_test AS BEGIN SELECT * FROM tblTest --Considering table has 3 columns. END How does C# gets this result into DataTable. Wh...

PostgreSQL Math Function Error

I'm getting an error from a deterministic PG function that seems dependent based on when I call it in a query. The function is: CREATE OR REPLACE FUNCTION ircm(starting_money numeric, value numeric, days numeric) RETURNS numeric AS $$ BEGIN -- Calculate effective interest rate, compounded monthly. RETURN 12*(pow((value/starting_mon...