I need an SQL guru to help me speed up my query. I have 2 tables, quantities and prices. quantities records a quantity value between 2 timestamps, 15 minutes apart. prices records a price for a given timestamp, for a given price type and there is a price 5 record for every 5 minutes. I need 2 work out the total price for each period, e...
SELECT challenger_name as name, IF(challenger_timeout > challenged_timeout, (SET wins + 1), NULL) as wins, IF(challenger_timeout < challenged_timeout, (SET lose + 1), NULL) as lose, IF(challenger_timeout = challenged_timeout, (SET draw + 1), NULL) as draw FROM time_trial_...
I have these tables: client idclient name reference address idaddress idclient inv idinvoice idaddress datetime total payment idpayment idinvoice pdatetime amount into - name, reference, time, idpayment, idinvoice, amount i.e. datetime and pdatetime as time. inv.total as negative amount. Where idaddress = 9 ...
I have two tables. I want to join them in a way that only one record in the right table is returned for each record in the left most table. I've included an example below. I'd like to avoid subqueries and temporary tables as the actual data is about 4M rows. I also don't care which record in the rightmost table is matched, as long as one...
I have a Bill table like this BillNo - 1, ServiceCode -1, ServiceName - 'Lab test' , ItemAmount -30 BillNo -1, ServiceCode -2, ServiceName -'Consultation', ItemAmount -70 I need output like this Bill No -1, Total Bill Amount-100 , Lab Test-30, Consultation-70, Drugs-0, Misc-0 So basically i need the Bill Amount which is the SUM...
*First, thanks for the help in the first part of that question: Situation again: "I have in my table "mytable" fields, entryid (autoincrement) and roomid.. and I would like to delete all roomid = 1, except the last 3" entryid, roomid 1 1 2 55 3 1 4 12 5 1 6 ...
I have 4 Tables (listed bellow) and need: get last 10 Chats from Room 3 without banned users show nickname for fromuserid HIDE Users $userid dont like to see table "HIDE" Table 1 "chats" ID(autoinc) fromuserid roomid text 1 23 3 bla 2 14 1 bla 3 11 ...
I have an array of IDs that are already sorted in descending order as to their relevancy, but I need to make a query to get the details of each ID. How can I get the results to return so that they are in the order of my Where statement? SELECT DISTINCT `content_object_set_instances`.`id` AS content_object_set_instance_id, `content_obje...
Suppose I have a table whose function is specifically to link two other tables in terms of OOP So suppose that I have two tables: one for person's name and another one for phone numbers Table 1: id person's name 1 John 2 Smith Table 2: id Phone number 5 23424224 6 23424242 And then I have a third table that links ...
Hello, I am having trouble getting my head around this query for some reason and I was wondering if someone would be able to help me out here. I have the following 3 tables: opp_cstm: id_c make_c time_followup_c lead_category_c lead_type_c 9 GMC 224 GM Internet Sales e Buick 8...
Hello guys I'm stucked at this point. So I need your help, thanks anyway. I'm using this code to count rows and trying to do IF num rows equals to ZERO do INSERT if not UPDATE but doesnt works. When I use "==" operator nothing happens, if I use ">=" operator script inserting the values but running insert query on every refresh and value...
I have some several codes in PHP to do jobs that MySQL could do. such as sorting, merging each data from different MySQL tables, etc... Lately, I found out that I can do all these stuffs with one MySQL query. I am wondering is it better to give the MySQL capable jobs to MySQL or to PHP. efficiencies, speed, etc.. Thank You, ...
Hi, I have a table containing integer values from 0 to some large number N, but has some number gaps. I'm trying to write an efficient query that'll find the first set of continuous values of some length that are within 0 and N but are not contained in the said table. This has applications to find unused ids, etc. For example, given th...
Heres my code <?php session_start(); include('config.php'); if(isset($_GET['search_word'])) { // echo $_GET['search_word'] . '<br />'; // debugging $search_word = $_GET['search_word']; $search_word = mysql_escape_string($search_word); $search_word_fix = str_replace(" ","%",$search_word); $query = "SELECT * FROM art...
How to fix this mysql query SELECT no, name, (SELECT chapter, max FROM table2 WHERE name = user.name && max = 10) as sub_array1, (SELECT chapter, max FROM table2 WHERE name = user.name && max = 20) as sub_array2 FROM user ORDER by exp DESC example expected out result: I want the data to be: data->name data->no data->sub_array1->chapt...
Hi. I have a query that look like this: $sql = "UPDATE tbl SET amt_field='amt_field+1' WHERE username='" .mysql_real_escape_string($_SESSION['username']). "'"; mysql_select_db('db',$con); mysql_query($sql,$con); I want to increment the value as easily as possible I have tried : "UPDATE tbl SET amt_field='amt_field+1'...
Hello everyone, I have a simple query as listed below SELECT id, name, email FROM users WHERE group_id = 1 This works great until, I then start adding LIKE queries, chained with OR statements to the end. SELECT id, name, email FROM users WHERE group_id = 1 AND id LIKE $searchterm OR name LIKE $searchterm OR email LIK...
I need to execute a query that is highly dependent on several conditions what need to be checked by analyzing that data in other tables, the base goal is to return the rows from the tables if all of the conditions in the other tables are true, I am fed up with INNER joins LEFT joins and WHERE statement, i need to look up one table, if th...
Hi, I have a table like id fid 20 53 23 53 53 53 Here I need to return true when my condition is something like .. where fid=53 and id in(20,23,53) and also I need to return false when....where fid=53 and id in(20,24,53). But the above condition not satisfy my requirement.Both queries returns same value(true).Please help me...
How to fix this mysql query SELECT no, name, (SELECT chapter, max FROM table2 WHERE name = user.name AND max = 10) as sub_array1, (SELECT chapter, max FROM table2 WHERE name = user.name AND max = 20) as sub_array2 FROM user ORDER by exp DESC example expected out result: the current query returns me Operand should contain 1 column(s) ...