Hey guys i am really messing up with this. I tried this but failed. I have following three tables with user polard searched 2 times at different days, with increased friends in second search in table b. I just need to build the query which will give me the two newly added friends (wont be present in end of list of second search). In tabl...
I have a varchar field with the following data: Interface Gig1/0/1 Gig1/0/3 Gig1/0/5 Gig1/0/10 Gig1/1/11 I am trying to do a search (BETWEEN). Select * from test1 where Interface Between "Gig1/0/1" and "Gig1/0/5" Returns all the records except for Gig1/1/11 ...
I have a method that loops through all the rows of a table where a certain condition is met, and then checks if one of the columns in the row appears in a text. The method is below: public function isRecipeType($ingredients, $type) { $rows = $this->fetchAll($this->select()->where("type = ?", $type)); foreach($rows as $row) ...
Hi All, I have a simple question regarding best practices in programming. I have a DB which contain members. After some processing I get a list of users who need to be disabled in DB. I have a stored procedure which takes UserId as Input and disable the user. Since I am sure that I always have to disable more than one user, I am thin...
SELECT u.id AS pid , b2.id AS id , b2.message AS MESSAGE, b2.uid AS uid, b2.date AS DATE FROM ( (SELECT b.id AS id , b.pid AS pid , b.message AS MESSAGE, b.uid AS uid, b.date AS DATE...
I have 2 columns in mysql table: a and b. a is allways string value and b is sometimes a string value and sometimes it is null. How to construct a mysql SELECT so that the b would be taken if it is not null and a would be taken otherwise. I tried to make some magic with concat and if...then with no success... Please help Jerry UPDAT...
I have a table house with fields price and area, now I want to add one more column named 'average_price'. How could I set the average_price according to the price and area in MYSQL? ...
Hi, I've been trying to work this our for a while, but having trouble. We have 4 tables consiting of, suppliers, supplier_areas, supplier_languages and supplier_products options. I am trying to make an advanced search where users can search members using any of the above. An example search may be all suppliers in a certain area that ...
Table a (Author Table) author_id author_name Table b (Post Table) post_id author_id Table c (Earning Table) post_id (post id is not unique) post_earning I wanted to generate a report consist of earnings per author. author_id author_name total_earning (sum of earnings of all the posts by author) The SQL Query used: SELECT...
Hi Guys, I have a MySQL statement that took me all night to come up with. I'm wondering if this thing can be converted from a direct call to something pretty like Object.find(:conditions) ActiveRecord::Base.connection.execute(" SELECT *, (SELECT COUNT(*) FROM scores AS temp2 WHERE temp2.score > scores.scor...
I'm having trouble with sorting out a query for the following: Data: Column1 Column2 2 0 0 -2 I'm trying to select the difference between Column1 and Column2, with a minimum of 0. Eg. Row1=2 Row2=0 My current query is SELECT (Column1 - Column2) as total FROM blah. I tried adding max(Column2, 0) into the query, but t...
Here is the code I have came up for this problem. $link = mysql_connect($host, $user, $pass) or die("Can not connect to the host." . mysql_error()); mysql_select_db($db) or die("Can not connect to the particular database."); $result = mysql_query("SHOW COLUMNS FROM ".$table.""); $i = 0; if (mysql_num_rows($result) > 0) { while ($r...
retrieve next 24 hours details from the table i read one thread, same question here link text but small diff is , my field name is jdate is the varachar, but they are used the datetime data type format, ...
I've got the following SQL, but I only want to return rows where 'hits' are greater than 10. SELECT clicks.affiliate, COUNT(*) AS hits, affiliates.title, affiliates.url FROM clicks INNER JOIN affiliates ON affiliates.id = clicks.affiliate GROUP BY clicks.affiliate Thanks. ...
I have a database containing two tables - one, states, contains info about each state's income taxes, and the second, brackets, contains the tax brackets for each state connected to States by a numeric key. I would like to use SQL to retrieve each state and the AVERAGE tax bracket amount for each state to output in a recordset. For exam...
I have a fairly large number of rows where a datetime field is stored such as: 0010-01-03 00:00:00 But, I need it to be: 2010-01-03 00:00:00 Any suggestions on how to mass change rows from 0010 to 2010? ...
I have a table of several hundred users and I want to create a 4-digit pin code like those used at an ATM machine for each user using an UPDATE statement. They don't have to be unique, but I don't want to use an autonumber type of field so one company cannot easily guess the pin code of another user. What is the simplest and easiest way ...
Query: SELECT * FROM (test) test_label WHERE REGEXP "AA(.*)BB" OR test_label REGEXP "BB(.*)AA"** Database (string): 1. AA BB 100 2. AA BB 200 3. BB AA 300 4. BB CC 100 5. AA CC 300 This query returns rows: 1, 2 and 3. How: Combine this expression into one (but - i want it with one REGEXP but not with OR - if it is possible it mus...
Is there any single query to delete the child tables if the parent table will be deleted? tables: t1 t1_id 1 t2 t2_id t1_id 1 1 2 1 t3 t3_id t2_id 1 1 2 1 3 1 t4 t4_id t3_id 1 1 2 1 So if I ...
hi i cannot update a table which has foreign keys on it. in this table, instead of displaying the primary keys of the foreign key, i choose to display their names: this is a simple diagram: Here are my foreign tables: Size Table: sId sName 1 1x1 2 2x2 Brand Table: bId bName 1 brand1 2 brand2 Supplier Table: sId sNa...