sql

Show names of suppliers who delivered largest delivery (nested query)?

I have a table S containing suppliers (S, SNAME, CITY), a table P containing products (P, PNAME, COLOUR, WEIGHT, PRICE) and a table SP containing deliveries (S, P, QTY) where S and P are foreign keys to table S and P. What I have been asked to do is "Show the name of the supplier(s) who have delivered the largest delivery (as a nest...

How to use Max function on another column from a select

There is query which is asking for favourite products which is bought by each coustomer. i have to select and in the first select i have selected the count of products that each customer bought. in the other select i want to select the maximum of that boughts for each customer.but when i want to select max(previous select column) it gets...

SQL: insert value into foreign key

Hello, Im using phpmyadmin for a website i'm creating and have a question about a inserting values into foreign key. Im able to select an image table id when event col in events table is value x i.e... $event = "Partay"; $sql = "SELECT i.ImageID FROM images i INNER JOIN events e ON i.eventID = e.eventID WHERE e.event ='".$event."'"...

How to efficiently remove all rows from a table in DB2

I have a table that has something like half a million rows and I'd like to remove all rows. If I do simple delete from tbl, the transaction log fills up. I don't care about transactions this case, I do not want to rollback in any case. I could delete rows in many transactions, but are there any better ways to this? How to efficiently r...

SQL Returned more than one Value

Im sure this is simple and i will kick myself when i find out but ive been sitting on this problem for the last hour and im getting annoyed with it, could anyone help me. So i am trying to enter a DeviceId and ConfigurationId Column the values are the primary keys from a Device table and a Configuration table. Yes thats really all the p...

SQLite - Create and index for every field

Hi, I'm creating a application that will fetch data from an SQLite database and display it in a table. I want the table to be updated in real-time as the user makes a selection (via multiple dropdown box). Every time the user selects an option from the dropdown boxes, the application will have to create a new SELECT query with a new W...

T SQL - Split and merge columns

Hi I've got a problem I've not come across before. I've got an Excel datasource I've loaded into SQL table and I am trying to transform into something a bit more sensible. What I've got is column A in the SQL table from Excel that contains all 3 fields of data but I need to add columns to replace the groups of Owner and Stage Note a u...

SQL change query to show orphans

Give the query: SELECT tblProducts.ID, tblProducts.views, tblProducts.productName, tblProducts.isForSale, tblProducts.isLimitedStock, tblProducts.stockCount, tblProducts.description, tblProducts.weightKG, tblProducts.basePrice, tblProducts.dateCreated, tblProductCats.catName, (SELECT COUNT(*) FROM tblProductPrices WHERE product...

How to Select Oldest Date in SQL?

I have this sql statement: SELECT TOP (5) PartNumber,SerialNumber,MIN(WIP_CompletionDate) as dates FROM dbo.FG_FILLIN where Status='FG-FRESH' and WIP_Status<>'CMPL01' and PartNumber='P02-070161-10211-C100' GROUP BY PartNumber,WIP_CompletionDate,SerialNumber Result: PartNumber ...

Implementation of "Most Viewed" feature database

Hi, I was wondering how to best implement a "most viewed" featured (like youtube) on my database. Let me explain the "most viewed" feature a little bit better: Basically I want to list the most visited pages/video/etc from this day/week/month, see http://www.youtube.com/charts/videos_views for an example. So I was wondering how to b...

Optimize SQL query when GROUP BY and ORDER BY expressions are different?

Hello all From the Order By Optimization in Mysql documentation, I quote... In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: You have different ORDER BY and GROUP BY expressions. Is the...

Invalid column name, on select-statement created columns

I have simplified my problem to the following select statement. select u.UserId, aVariable = cast((case when exists(select * from TblUser u where u.Disabled=1) then 1 else 0 end) as bit), from TblUser u where aVariable = 1 aVariable is not a column of a table, but just a column that gets a value in this select statement. Is ...

SQL Script to findout Numeric Value

I need to filter it out the number from my table. for example, Id url 001 /testing 002 /testing2 003 /24678 From the above table, i need to fetch the numeric value ( 24678 - which is dynamically creating) and should update with some string like "my-testing". how to write SQL script for this? thanks in advance. Liy...

How to create a view and select from it in one SINGLE query execution

I want to create a veiw and then select from it in one query, would you please tell me how it is possible the code below: create view myView as select [Order Details].Discount from [Order Details] select * from myView if i want to run the code, first i have to select that part which is related to creating a view and then i have to sel...

Merge statement, where you insert or delete depending on parameter

I want to make an efficient sproc that either inserts a row or deletes. If a row exist, and the @action is false, i want to delete the row. If the row exists but @action is set to true no actions should be performed If the row doesnt exist i want to insert it Its for a checkboxlist, so i was going to loop through each listItem and call...

Error with calling stored procedure from C#

I have the following c# code to call stored procedure testproc, but when i run this application it displays saying that could not find stored procedure testproc, do i need to make any changes in syntax to call stored procedure. This is my c# code behind to call stored procedure SqlConnection con = new SqlConnection(); con.ConnectionStri...

in Postgresql how to speed up substring queries

I have a simple logs table with about 500,000 rows, table structure is TABLE logs ( id serial NOT NULL, username character varying(32), user_id integer, description text NOT NULL, "time" timestamp with time zone DEFAULT now(), referrer character varying(128), "type" character varying(25) ) The most common operation in t...

Problem with SQLBrowseConnect

I'm making a call to odbc32.dll (SQLBrowseConnect) to return a list of databases on a sql server. From running a trace I can see the query being executed is select name from master..sysdatabases where has_dbaccess(name)=1 If the credentials I pass aren't the sa user it returns just the system databases. Is there anyway I can use SQL...

PHP SQL UPDATE works in FF and CHROME but not in IE?

Hi, the below code works perfectly in FF and CHROME but not in IE. Please help. I have commented out my santize functions as i thought they might be affecting it, but it still does the same.... nothing in IE. Thank you in advance for any assistance. <?php //IF UPDATE BUCKET CHANGE STATUS... if(isset($_POST['updatebucket'])){ $com...

Liquibase drop constraint without knowing it's name

We use liquibase to keep track of our database changes.. First changeSet contains those lines: <column name="SHORT_ID" type="INTEGER"> <constraints unique="true" /> </column> Basically it means that SHORT_ID column has unique constraint but the name of this constraint can be whatever and usually is different each time (we run some ...