Search Results

Search found 1369 results on 55 pages for 'over clause'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 14/55 | < Previous Page | 10 11 12 13 14 15 16 17 18 19 20 21  | Next Page >

  • regular expression or replace function in where clause of a mysql query.

    - by Salil
    Hi All, I write a mysql query select * from table where name like '%salil%' which works fine but it will no return records with name 'sal-il', 'sa@lil'. So i want a query something like below select * from table where remove_special_character_from(name) like '%salil%' remove_special_character_from(name) is a mysql method or a regular expression which remove all the special characters from name before like executed.

    Read the article

  • Is there any way to optimize this LINQ where clause that searches for multiple keywords on multiple

    - by Daniel T.
    I have a LINQ query that searches for multiple keywords on multiple columns. The intention is that the user can search for multiple keywords and it will search for the keywords on every property in my Media entity. Here is a simplified example: var result = repository.GetAll<Media>().Where(x => x.Title.Contains("Apples") || x.Description.Contains("Apples") || x.Tags.Contains("Apples") || x.Title.Contains("Oranges") || x.Description.Contains("Oranges") || x.Tags.Contains("Oranges") || x.Title.Contains("Pears") || x.Description.Contains("Pears") || x.Tags.Contains("Pears") ); In other words, I want to search for the keywords Apples, Oranges, and Pears on the columns Title, Description, and Tags. The outputted SQL looks like this: SELECT * FROM Media this_ WHERE (((((((( this_.Title like '%Apples%' or this_.Description like '%Apples%') or this_.Tags like '%Apples%') or this_.Title like '%Oranges%') or this_.Description like '%Oranges%') or this_.Tags like '%Oranges%') or this_.Title like '%Pears%') or this_.Description like '%Pears%') or this_.Tags like '%Pears%') Is this the most optimal SQL in this case? If not, how do I rewrite the LINQ query to create the most optimal SQL statement? I'm using SQLite for testing and SQL Server for actual deployment.

    Read the article

  • Will Apple's new "originally written in" clause affect your decision to target the iPhone?

    - by Michael Aaron Safyan
    So, you've probably heard about Apple's change to its agreement to prohibit source-to-source translation, thereby blocking translation from Flash (in CS5) and also from Android (via XMLVM). You may also have read about a response by a well-known Adobe developer, and calls to boycott development for the iPhone. Given that this audience is a better representative of the developer community than those who post comments on the NYT, Digg, and other news sites, I was wondering what your opinions were about this decision. Will any of you switch to Android from the iPhone or avoid development on the iPhone as a result of this? Since this is fairly subjective, I am making this a community wiki. Also, please, keep things civil.

    Read the article

  • MySQL - Calculating a value from two columns in each row, using the result in a WHERE or HAVING clause

    - by taber
    I have a MySQL db schema like so: id flags views done ------------------------------- 1 2 20 0 2 66 100 0 3 25 40 0 4 30 60 0 ... thousands of rows ... I want to update all of the rows whose flags / views are = 0.2. First as a test I want to try to SELECT to see how many rows would actually get updated. I tried: SELECT flags/views AS poncho FROM mytable HAVING poncho 0.2 But this only returns like 2 rows and I know there should be a lot more than that. It seems like it's calculating the poncho value on all rows or something odd. What am I doing wrong? Thanks!

    Read the article

  • MYSQL fetch 10 posts, each w/ vote count, sorted by vote count, limited by where clause on posts

    - by nibblebot
    I want to fetch a set of Posts w/ vote count listed, sorted by vote count (e.g.) Post 1 - Post Body blah blah - Votes: 500 Post 2 - Post Body blah blah - Votes: 400 Post 3 - Post Body blah blah - Votes: 300 Post 4 - Post Body blah blah - Votes: 200 I have 2 tables: Posts - columns - id, body, is_hidden Votes - columns - id, post_id, vote_type_id Here is the query I've tried: SELECT p.*, v.yes_count FROM posts p LEFT JOIN (SELECT post_id, vote_type_id, COUNT(1) AS yes_count FROM votes WHERE (vote_type_id = 1) GROUP BY post_id ORDER BY yes_count DESC LIMIT 0, 10) v ON v.post_id = p.id WHERE (p.is_hidden = 0) ORDER BY yes_count DESC LIMIT 0, 10 Correctness: The above query almost works. The subselect is including votes for posts that have is_hidden = 1, so when I left join it to posts, if a hidden post is in the top 10 (ranked by votes), I can end up with records with NULL on the yes_count field. Performance: I have ~50k posts and ~500k votes. On my dev machine, the above query is running in .4sec. I'd like to stay at or below this execution time. Indexes: I have an index on the Votes table that covers the fields: vote_type_id and post_id EXPLAIN id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY p ALL NULL NULL NULL NULL 45985 Using where; Using temporary; Using filesort 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 2 DERIVED votes ref VotingPost VotingPost 4 319881 Using where; Using index; Using temporary; Using filesort

    Read the article

  • How can I do a right outer join where both tables have a where clause?

    - by cdeszaq
    Here's the scenario: I have 2 tables: CREATE TABLE dbo.API_User ( id int NOT NULL, name nvarchar(255) NOT NULL, authorization_key varchar(255) NOT NULL, is_active bit NOT NULL ) ON [PRIMARY] CREATE TABLE dbo.Single_Sign_On_User ( id int NOT NULL IDENTITY (1, 1), API_User_id int NOT NULL, external_id varchar(255) NOT NULL, user_id int NULL ) ON [PRIMARY] What I am trying to return is the following: is_active for a given authorization_key The Single_Sign_On_User.id that matches the external_id/API_User_id pair if it exists or NULL if there is no such pair When I try this query: SELECT Single_Sign_On_User.id, API_User.is_active FROM API_User LEFT OUTER JOIN Single_Sign_On_User ON Single_Sign_On_User.API_User_id = API_User.id WHERE Single_Sign_On_User.external_id = 'test_ext_id' AND API_User.authorization_key = 'test' where the "test" API_User record exists but the "test_ext_id" record does not, and with no other values in either table, I get no records returned. When I use: SELECT Single_Sign_On_User.id, API_User.is_active FROM API_User LEFT OUTER JOIN Single_Sign_On_User ON Single_Sign_On_User.API_User_id = API_User.id WHERE API_User.authorization_key = 'test' I get the results I expect (NULL, 1), but that query doesn't allow me to find the "test_ext_id" record if it exists but would give me all records associated with the "test" API_User record. How can I get the results I am after?

    Read the article

  • How to have a where clause on an insert or an update in Linq to Sql?

    - by Kelsey
    I am trying to convert the following stored proc to a LinqToSql call (this is a simplied version of the SQL): INSERT INTO [MyTable] ([Name], [Value]) SELECT @name, @value WHERE NOT EXISTS(SELECT [Value] FROM [MyTable] WHERE [Value] = @value) The DB does not have a constraint on the field that is getting checked for so in this specific case the check needs to be made manually. Also there are many items constantly being inserted as well so I need to make sure that when this specific insert happens there is no dupe of the value field. My first hunch is to do the following: using (TransactionScope scope = new TransactionScope()) { if (Context.MyTables.SingleOrDefault(t => t.Value == in.Value) != null) { MyLinqModels.MyTable t = new MyLinqModels.MyTable() { Name = in.Name, Value = in.Value }; // Do some stuff in the transaction scope.Complete(); } } This is the first time I have really run into this scenario so I want to make sure I am going about it the right way. Does this seem correct or can anyone suggest a better way of going about it without having two seperate calls? Edit: I am running into a similar issue with an update: UPDATE [AnotherTable] SET [Code] = @code WHERE [ID] = @id AND [Code] IS NULL How would I do the same check with Linqtosql? I assume I need to do a get and then set all the values and submit but what if someone updates [Code] to something other than null from the time I do the get to when the update executes? Same problem as the insert...

    Read the article

  • Does the order of columns matter in a group by clause?

    - by Jeff Meatball Yang
    If I have two columns, one with very high cardinality and one with very low cardinality (unique # of values), does it matter in which order I group by? Here's an example: select dimensionName, dimensionCategory, sum(someFact) from SomeFact f join SomeDim d on f.dimensionKey = d.dimensionKey group by d.dimensionName, -- large number of unique values d.dimensionCategory -- small number of unique values Are there situations where it matters?

    Read the article

  • How do I add complex where clause to Zend Table Select?

    - by AD
    I searched the Web and could not find anything that would show me a good solid example. My question is basically this: How do I convert this: SELECT * FROM table WHERE ((a = 1 AND b = 2) OR (c = 3 OR c = 4)) AND d = 5; To Zend syntax similar to this: $this -select() -from($this-_schema.'.'.$this-_name) -where('a = ?', '1'); So how can it be done? Thank a lot in advance.

    Read the article

  • How do I correctly use two Not Exists statements in a where clause using Access SQL VBA?

    - by Bryan
    I have 3 Tables: NotHeard,analyzed,analyzed2. In each of these tables I have two columns named UnitID and Address. What I'm trying to do right now is to select all of the records for the columns UnitID and Address from NotHeard that don't appear in either analyzed or analyzed2. The SQL statement I created was as follows: SELECT UnitID, Address INTO [NotHeardByEither] FROM [NotHeard] Where NOT EXISTS( Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID) or NOT EXISTS( Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID) Group BY UnitID, Address I thought this would work since I've used the single NOT EXISTS subquery line and it has worked just fine for me in the past. The above query however returns the same data that is in the NotHeard table whereas if I take out the or NOT EXISTS part it works correctly. Any ideas as to what I'm doing wrong or how to do what I'm wanting to do?

    Read the article

  • How to update a sharepoint list item via web services using a where clause?

    - by JL
    I would like to update a list item using SharePoint and am stuggling to find 1 decent CAML example. Here is what I want to do, in SQL my query would look something like this update [table] set field='value' where fieldID = id; so this would mean I have 1 item in a list I would like to update 1 field on given the ID of that listitem. I've tried this, but it doesn't work: batchElement.InnerXml = "<Method ID='1' Cmd='Update'>" + "<Field Name='DeliveryStatus'>" + newStatus.ToString() + "</Field>" + "<Where><Eq><FieldRef Name='ID' /><Value Type='Text'>" + id + "</Value></Eq></Where></Method>";

    Read the article

  • MS Acess SQL - where clause to get year from date based on the year - data located in MS access form

    - by primus285
    OK, back again. I have a problem getting a drop down list to populate based on information in two fields. I have got the SQL correct as to Select just one year if I put DateValue('01/01/2001') in both places, but I am trying now to get it to grab the year data from the MS access form - another drop down named "cboYear". I'd hate to have to do something in VB, unless necessary. so far I have gotten this to pull up something (its always incorrect) SELECT DISTINCT Database_New.ASEC FROM Database_New WHERE Database_New.Date>=DateValue('01/01/' & [cboYear]) And Database_New.Date<=DateValue('12/31/' & [cboYear]); and SELECT DISTINCT Database_New.ASEC FROM Database_New WHERE Database_New.Date>=DateValue('01/01/' + [cboYear]) And Database_New.Date<=DateValue('12/31/' + [cboYear]); SELECT DISTINCT Database_New.ASEC FROM Database_New WHERE Database_New.Date>=DateValue('01/01/' AND [cboYear]) And Database_New.Date<=DateValue('12/31/' AND [cboYear]); both give errors saying that it is too complex to compute. Its probably something simple, but where do I go from here?

    Read the article

  • Is using joins in select clause slow in Oracle?

    - by gniquil
    I would like to write a query like the following select username, (select state from addresses where addresses.username = users.username) email from users This works in Oracle (assuming the result from the inner query is unique). However, is there a performance penalty associated with this style of writing query?

    Read the article

  • Is there a more elegant solution than an if-statement with no else clause?

    - by Jay
    In the following code, if Control (the element that trigers Toggle's first OL) is not Visible it should be set Visible and all other Controls (Controls[i]) so be Hidden. .js function Toggle(Control){ var Controls=document.getElementsByTagName("ol",document.getElementById("Quote_App")); var Control=Control.getElementsByTagName("ol")[0]; if(Control.style.visibility!="visible"){ for(var i=0;i<Controls.length;i++){ if(Controls[i]!=Control){ Reveal("hide",20,0.3,Controls[i]); }else{ Reveal("show",20,0.3,Control); }; }; }else{ Reveal("hide",20,0.3,Control); }; }; Although the function [Toggle] works fine, it is actually setting Controls[i] to Hidden even if it is already. This is easily rectified by adding an If statement as in the code below, surely there is a more elegant solution, maybe a complex If condition? .js function Toggle(Control){ var Controls=document.getElementsByTagName("ol",document.getElementById("Quote_App")); var Control=Control.getElementsByTagName("ol")[0]; if(Control.style.visibility!="visible"){ for(var i=0;i<Controls.length;i++){ if(Controls[i]!=Control){ if(Controls[i].style.visibility=="visible"){ Reveal("hide",20,0.3,Controls[i]); }; }else{ Reveal("show",20,0.3,Control); }; }; }else{ Reveal("hide",20,0.3,Control); }; }; Your help is appreciated always.

    Read the article

  • How do I workaround this error ? You can't specify target table my table for update in FROM clause

    - by Jules
    I'm struggling to convert my select query into a update query, it has aliases.. Update pads set RemoveMeDate = '1999-01-01 00:00:00' where padid in ( SELECT old_table.padid FROM `jules-fix-reasons`.`pads` AS old_table JOIN `jules`.`pads` AS new_table ON old_table.padid = new_table.`PadID` WHERE new_table.RemoveMeDate <> '2001-01-01 00:00:00' AND old_table.RemoveMeDate = '2001-01-01 00:00:00') I've tried removing the aliases, but that doesn't help :(

    Read the article

  • Any way to avoid a filesort when order by is different to where clause?

    - by Julian
    I have an incredibly simple query (table type InnoDb) and EXPLAIN says that MySQL must do an extra pass to find out how to retrieve the rows in sorted order. SELECT * FROM `comments` WHERE (commentable_id = 1976) ORDER BY created_at desc LIMIT 0, 5 exact explain output: table select_type type extra possible_keys key key length ref rows comments simple ref using where; using filesort common_lookups common_lookups 5 const 89 commentable_id is indexed. Comments has nothing trick in it, just a content field. The manual suggests that if the order by is different to the where, there is no way filesort can be avoided. http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html I also tried order by id as well as it's equivalent but makes no difference, even if I add id as an index (which I understand is not required as id is indexed implicitly in MySQL). thanks in advance for any ideas!

    Read the article

  • How to use data of one table in 'where' clause of another table?

    - by sahar
    hello, i need ur help guys..i m making website for 'home docor ideas'..i have a log in form(login-form.php) in which when 'log in' and 'password' is inserted,after verification through login-execute.php, redirected to viewOrder.php where user can view all of the orders ordered by clients.. all is fine up till here.. but what i want is,when user get logged in ,he view only that order which is ordered by him not all customer's orders.. two tables are there in database: members and order_insert.. in 'members' table, login and password is stored and in 'order_insert',orders of customers is stored.. codes of these three pages is as follows.. ......................... login-form.php ......................... <form id="loginForm" name="loginForm" method="post" action="login-exec.php"> <table width="300" border="0" align="center" cellpadding="2" cellspacing="0"> <tr> <td width="112"><b>Login</b></td> <td width="188"><input name="login" type="text" class="textfield" id="login" /></td> </tr> <tr> <td><b>Password</b></td> <td><input name="password" type="password" class="textfield" id="password" /></td> </tr> <tr> <td>&nbsp;</td> <td><input type="submit" name="Submit" value="Login" /></td> </tr> </table> </form> ......................... login-execute.php ......................... <?php //Start session session_start(); //Include database connection details require_once('config.php'); //Array to store validation errors $errmsg_arr = array(); //Validation error flag $errflag = false; //Connect to mysql server $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if(!$link) { die('Failed to connect to server: ' . mysql_error()); } //Select database $db = mysql_select_db(DB_DATABASE); if(!$db) { die("Unable to select database"); } //Function to sanitize values received from the form. Prevents SQL injection function clean($str) { $str = @trim($str); if(get_magic_quotes_gpc()) { $str = stripslashes($str); } return mysql_real_escape_string($str); } //Sanitize the POST values $login = clean($_POST['login']); $password = clean($_POST['password']); //Input Validations if($login == '') { $errmsg_arr[] = 'Login ID missing'; $errflag = true; } if($password == '') { $errmsg_arr[] = 'Password missing'; $errflag = true; } //If there are input validations, redirect back to the login form if($errflag) { $_SESSION['ERRMSG_ARR'] = $errmsg_arr; session_write_close(); header("location: login-form.php"); exit(); } //Create query $qry="SELECT * FROM members WHERE login='$login' AND passwd='".md5($_POST['password'])."'"; $result=mysql_query($qry); //Check whether the query was successful or not if($result) { if(mysql_num_rows($result) == 1) { //Login Successful session_regenerate_id(); $member = mysql_fetch_assoc($result); $_SESSION['SESS_MEMBER_ID'] = $member['member_id']; $_SESSION['SESS_FIRST_NAME'] = $member['firstname']; $_SESSION['SESS_LAST_NAME'] = $member['lastname']; session_write_close(); header("location: viewOrder.php"); exit(); }else { //Login failed header("location: login-failed.php"); exit(); } }else { die("Query failed"); } ?> ............................. viewOrder.php .............................. <html> <body bgcolor="#FFFFFF" > <? $host="localhost"; // Host name $username="root"; // Mysql username $password=""; // Mysql password $db_name="mydatabase"; // Database name $tbl_name="order_insert"; // Table name $tbl_name2="members"; // connect to server and databases mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $result = mysql_query("SELECT * FROM $tbl_name "); print "<center>"; $output .= "<table width=1100 border=1 bordercolor=black>"; $output .= "<tr align=center><td>ID</td><td>First Name</td><td>Last Name</td><td>E Mail</td><td> City </td><td> Country </td><td> Phone</td><td>Decoration Type</td><td>Service Description</td><td>Budget</td><td>Update</td><td>Delete</td></tr>"; $output .= "<th></th><th></th>"; $output .= "</tr>\n\n"; while ($row = mysql_fetch_assoc($result)){ $output .= "<tr>\n"; foreach ($row as $col=>$val){ $output .= " <td>$val</td>\n"; } // end foreach $keyVal = $row["id"]; $output .= "<td><a href='update.php?ID=$row[orderId]' >Update </a></td>"; $output .= "<td><a href='delete.php?ID=$row[orderId]' >Delete </a></td>"; $output .= "</tr>\n\n"; }// end while $output .= "</table></center>"; print "$output"; ?>&nbsp;&nbsp;&nbsp;<br> <br> <center><table > <tr><td> <form action="home.php"><font color="#FF0000"><input type="submit" name="btn" style="color:#CC0000" value="<--Back" ></font></form></td></tr></table></center> </body> </html> ..... your help and suggestions will be appreciated

    Read the article

  • What is the performance of "Merge" clause in sql server 2008?

    - by ziang
    Hi, Merge can performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. Is anyone familiar with the performance to use "Merge" versus the traditional logic to check existence and decide the update or insert then? Thanks!

    Read the article

  • With SQL can you use a sub-query in a WHERE LIKE clause?

    - by Jason
    I'm not even sure how to even phrase this as it sounds weird conceptually, but I'll give it a try. Basically I'm looking for a way to create a query that is essentially a WHERE IN LIKE SELECT statement. As an example, if I wanted to find all user records with a hotmail.com email address, I could do something like: SELECT UserEmail FROM Users WHERE (UserEmail LIKE '%hotmail.com') But what if I wanted to use a subquery as the matching criteria? Something like this: SELECT UserEmail FROM Users WHERE (UserEmail LIKE (SELECT '%'+ Domain FROM Domains)) Is that even possible? If so, what's the right syntax?

    Read the article

  • How to execute query with IN clause in spring?

    - by GK
    I have following code : try { MapSqlParameterSource parameters = new MapSqlParameterSource(); parameters.addValue("ids", year); return this.template.getJdbcOperations().query( "SELECT * FROM INCOME WHERE PROVNUM=? AND FISCALDATE IN ( :ids )", this.rowMapper, parameters); }catch (EmptyResultDataAccessException ex) { return null; } But i am not able to send the value for PROVNUM. how to do that? need help, thanks.

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 10 11 12 13 14 15 16 17 18 19 20 21  | Next Page >