where-clause

MySQL - ORDER BY values within IN()

Hey -- I'm hoping to sort the items returned in the following query by the order they're entered into the IN() function. INPUT: SELECT id, name FROM mytable WHERE name IN ('B', 'A', 'D', 'E', 'C'); OUTPUT: | id | name | ^--------^---------^ | 5 | B | | 6 | B | | 1 | D | | 15 | E | | ...

How can I get an OR in my Where clause?

I'm building an ad-hoc query to send to SQL doing the following: var data = from d in db.xxxx select d; foreach (pattern in user_stuff) data = data.Where(d=>SqlMethods.Like(d.item,pattern)==true); The problem is that the WHERE clauses get AND'ed together. I need OR's. Any idea how to get an OR? ...

When to use lambda expressions instead of a Where clause in LINQ

I've been really digging into LINQ, and I'm trying to hash out this lambda expression business. I'm just not seeing the benefit of some of the nuances of the syntax. Primarily, it seems to me that a lambda expression is mostly just a different way of using a Where clause. Why wouldn't I just use a Where clause then? Is the lambda expres...

How to write the Where clause in my ObjectQuery?

Please see my code: var miportal = new AdventureWorksEntities(); // one to many relationship var result = miportal.AddressType .Include("CustomerAddress") .Where(at => at.CustomerAddress.Any(ca => ca.CustomerID == 3)); there are 2 tables; AddressType and CustomerAddress (1 CustomerAddress has...

select with likewise data

I have a table in database that is having some fields one of which is 'action' action is having data like bse-similar,bse-action.....nse-similar,nse-action...etc. now i want to fetch the data that is having 'bse' in its action field. How can i do that in mysql????? One more thing i want to copy this data to another table.How can i do...

Oracle performance using functions in where clause

Hi In a stored procedure (which has a date parameter named 'paramDate' ) I have a query like this one select id, name from customer where period_aded = to_char(paramDate,'mm/yyyy') will Oracle convert paramDate to string for each row? I was sure that Oracle wouldn't but I was told that Oracle will. In fact I thought that if the param...

Negating an arbitrary where clause condition (including the null tests)

I want to efficiently check if a table contains any rows that match <condition A> and do not match <condition B>, where the conditions are arbitrary. In Oracle, this almost works: select count(*) from dual where exists ( select * from people where (<condition A>) and not (<condition B>) ); -- returns zero if all rows that match <...

Is a JOIN faster than a WHERE ?

Suppose I have two tables that are linked (one has a foreign key to the other) : CREATE TABLE Document ( Id INT PRIMARY KEY, Name VARCHAR 255 ) CREATE TABLE DocumentStats ( Id INT PRIMARY KEY, DocumentId INT, -- this is a foreign key to table Document NbViews INT ) I know, this is not the smartest way of doing things, but t...

Query related data based on "like" statement

I have two tables Table1: ColumnA (varchar) Table2: ColumnB (varchar) I need to get all rows from T2 where ColumnB is "like" any of the rows from 'ColumnA%'. For example, rows in T1 might be: Summer09 Fall09 while rows in T2 might be Spring09 Com101 Sec1 Summer09 Stat400 Sec2 Fall09 CS200 Sec3 In that scenario it would retun...

Update SQL with consecutive numbering

I want to update a table with consecutive numbering starting with 1. The update has a where clause so only results that meet the clause will be renumbered. Can I accomplish this efficiently without using a temp table? ...

Evaluation of multiples 'IN' Expressions in 'WHERE' clauses in mysql

Updating by @Cesar's request. Hope I understood what you want, if not, please revert. Quassnoi. If I make an SQL query like this: SELECT * FROM TABLE_NAME WHERE b IN (2, 7) AND c IN (3, 9), can I assume that MySQL will match only pairs from elements with same number in each list? That is, (2, 3), (7, 9), ...? For example, suppose we h...

Which SQL do you write?

When joining two tables, what are the difference between the two blocks below and which is the better approach? Pattern A: SELECT ... FROM A INNER JOIN B ON A.PK = B.FK WHERE 1=1 AND A.Name = "Foo" AND B.Title = "Bar" Pattern B: SELECT ... FROM A INNER JOIN B ON A.PK = B.FK AND B.Title = "...

Case statement in where clause w/an OR

Hey guys, Apologies in advance since I feel like I'm probably forgetting/missing something obvious on this one. Here goes; I'm using a case statement in my WHERE clause, the below works fine: WHERE r.[SomeCol] = @SomeColVal AND SomeOtherCol = ( CASE WHEN (@Year = 0 AND @Period = 0) THEN @SomeVal CASE WHEN... ... CASE ELSE @SomeVal EN...

Multiple Defered WHERE clause expressions in LINQ to SQL

Maybe a simple question, I'm trying to get a result from a table where the Name column contains all of an array of search terms. I'm creating a query and looping through my search strings, each time assigning the query = query.Where(...);. It appears that only the last term is being used, I supposed because I am attempting to restrict th...

Scala equivalent to Haskell's where-clauses?

Is it possible to use something similar to where-clauses in Scala? Maybe there is some trick I didn't think of? Edit: Thanks for all your answers, they are very much appreciated. To sum up: Local vars, vals and defs can be used to achieve almost the same thing. For lazy evaluation, one can use lazy val (with implicit caching) or functi...

linq where clause not in select statement

Can someone help me to convert from SQL Query to LINQ VB.NET: select rls.* from Roles rls(nolock) where rls.id not in ( select r.ID from usersRole ur (nolock) inner join Roles r(nolock) on ur.RoleID = r.ID where user_id = 'NY1772') Thanks ...

Using a var based on an enum in a Where clause in Entity Framework throws an exception.

I have the following code which throws an exception (detail in code comments below). I am merely trying to use an instance of an enum as part of the Where clause. I understand the message, but I don't understand why EF cannot parse an Int32 enum. It works if I copy the enum to an Int32 and then filter on that, but it seems very messy. ...

Complex Expressions in a LINQ Where Clause

I was wondering if it is possible to include inner variables or delegates in linq statements? I currently am using Linq to XML with XPath extensions and am using a where clause on an element that I cannot guarantee will exist. Here is a sample of what I mean: var result = from record in xml.Root.XPathSelectElements("record") w...

Specify more than one item in the where clause

Is there any way i could run the following 'logical code' to actually work? $sql=mysql_query("DELETE FROM users WHERE id='3,4,5,9'"); I basically want to give my user a tick box to tick for all displayed rows, they can then pick which ones to remove... i just want to remove more than one row with the id's specified? Any ideas? ...

Incorrect result for date comparison (to a timestamp column) in mysql

I've been having some 'strange' results while comparing dates. table1 has two rows with TIMESTAMPS values 2009-08-26 23:39:56 and 2009-08-27 00:01:42 When I make this query: select * from table1 c INNER JOIN table2 r ON r.table1_id = c.id WHERE DATE(c.authorization_date) = '2009-08-26' it returns both rows (when it only should have re...