select

MySQL - How do I get a range from the point closest to start?

Table structure- int PrimaryKey DateTime Date int Price What I would like to do is pass in a start and end date, and get the range From: The greatest date that is less than or equal to start UNLESS there is no such date, in which case the start of the range should be the lowest date available. To: The end date. My query so far i...

Oracle: Using Pseudo column value in the same Select statement

I have a scenario in oracle where i need to be able to reuse the value of a pseudo column which was calculated previously within the same select statement something like: select 'output1' process, process || '-Output2' from Table1 I don't want to the repeat the first columns logic again in the second column for maintenance purposes, c...

Select Fails With Nonexisitent Columns

Executing the following statement with SQL Server 2005 (My tests are through SSMS) results in success upon first execution and failure upon subsequent executions. IF OBJECT_ID('tempdb..#test') IS NULL CREATE TABLE #test ( GoodColumn INT ) IF 1 = 0 SELECT BadColumn FROM #test What this means is that something is co...

SQL Query, Selecting 5 most recent in each group

I have this table CREATE TABLE `codes` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `language_id` int(11) unsigned NOT NULL, `title` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 lan...

Error when comparing values in Oracle

I write simple procedure. DECLARE connection_id LINE.CONNECTION_ID%TYPE := 11009; tmp_integer INTEGER; BEGIN SELECT COUNT(*) INTO tmp_integer FROM LINE WHERE LINE.CONNECTION_ID = 11009; DBMS_OUTPUT.PUT_LINE(connection_id); DBMS_OUTPUT.PUT_LINE(tmp_integer); END; Result of the launch: 11009 3 It is good result. I have only...

MySQL Query with some type of join? Not sure.

If anyone could recommend a good book for learning mySQL as well, that would be great :). I have two tables, tags, codes_tags CREATE TABLE `tags` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCRE...

Select Count Distinct

I would like to count the number of installations of each Member in a table similar to this. But this count distinct drives me nuts... MemberID | InstallDate 1 | Yesterday 2 | Today 1 | Today 3 | Today The above table should produce something like this one.. MemberID | CountNumberOfInstallations 1 | 2 2 | 1 3 | 1 p.s. I know...

Reaction on checkbox works only once. What is wrong?

Hello, I am making the form where checkbox controls view of the radiobuttons in the row with this checkbox. The problem that it works only once. Please advise what can be wrong. The code of the page is following: <html> <head> <title></title> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <script ty...

Select the correct entry in a drop down list on the basis of database entry?

I have a simple Crud application where there is one filed called Category. During Creation , for Category I have provided a simple drop down box which lists all categories. During Editing, I need to get the same drop down box with the entry in the database chosen.How do i do this. For creation i used say <p><b>Category:</b>&nbsp;&nbsp;...

Merging two SQL SELECT statements

I need to merge two SELECT statements. There are two tables in my database, where table A consists of two fields; Type and Counter. Table B consists of two fields; Source and destination. Table A has three items; { 1, 5000 }, { 2, 10000 } and { 3, 15000 }. ({Type, Counter}) Table B has two items; ...

Get percentage average

I'm unsure how to go about this either because it late, or I'm a MySQL noob (probably both to be honest). I have a table called 'poll_votes' which comprises of: ID, poll_id, answer_id, ip, time_posted What I need to do, is for each unique answer_id in a specific poll_id, calculate the percentage out of the total number of answers th...

SQL select. Help please

Hi! I have a 3 tables 1. tBooks id Title authorID 2. tAuthors id Name 3. TBookAuthors bookID authorID a book can have a few authors, an author can write a few books So i need to search a book by name and select all the authors in the one record. i tried like this, but result is a few records (how much authors). Only diference b...

Select returns 0 rows in a cursor and right number of rows in manual running

Hello: I've this cursor declaration: DECLARE CursorArticulo CURSOR FOR SELECT HstAt.IdArticulo, SUM(HstAt.Cantidad) As SumaCantidad, HstAt.Precio FROM HstArticulosTickets hstAT INNER JOIN HstTickets HstT ON hstAT.IdTicket=hstT.IdTicket WHERE hstT.NumUsuarioEmisor=@UsuarioAct ...

dojo filteringSelect is very slow

Hello friends, In my project I am fetching cities using country selected. Now I have about 8000 cities for 1 country, and because of that filteringSelect takes time to show it fast. following are ajax functions I am using to fetch records and display it in my page. function GetCities(){ dojo.xhrPost({ url: 'GetCitiesForCountry....

Sql Server - Capture an XML query and save it to a table?

I would like to run a (extensive) query that produces one line of XML. This XML represents about 12 tables worth of relational data. When I "delete" a row from the top level table I would like to "capture" the state of the data at that moment (in XML), save it to an archive table, then delete all the child table data and finally mark t...

SQL Server recursive CTE and paging

I have this table CREATE TABLE [dbo].[friend_blocked_list]( [subdomain] [varchar](50) NOT NULL, [un] [nvarchar](50) NOT NULL, [friend] [nvarchar](50) NOT NULL, [is_blocked] [bit] NOT NULL, [approved] [bit] NOT NULL) where i select data from it with below query. Select query combines users that added user as friend and users that...

Visually Display Database Information with PHP/MySQL

Hi, I'm looking for a way to visually display my users' (schedules) at a glance. This is what my tables essentially look like: users: usrID usrFirst usrLast etc.. 22 John Doe semesters: id name year 1 Spring 2009 class_info: id name building ro...

Linq to SQL: Get top 10 most ordered products

I'm wanting to grab the 10 most ordered products. My tables look similar to this: ProductProductID | ProductName OrderedProductProductID | OrderID OrderOrderID | DateOrdered At the moment I've got the following: return (from product in db.Products from orderedProduct in db.OrderedProducts where orderedProduct.Product...

SQL Select help - removing white space

Hello all, I'm running into a problem when trying to select records from my 2005 MS-SQL database (I'm still very new to SQL, but I learned and use the basic commands from w3schools already). In theory, all my manufacturer records should be unique. At least that is how I intended it to be when I did my first massive data dump into it. Un...

Not able to pass value from a select list in a simple crud form?

Hi, I have written a simple CRUD form which has one select list. However the value from the select list doesnot seem to get passed to the database. it get passed when I am using a simple text box. What is the possible error? Please point out <?php include('config.php'); if (isset($_POST['submitted'])) { foreach($_POST AS $key => $value...