where-clause

Use terms from text file in SQL WHERE ... IN clause

I'm running SQL query for MySQL server with ... where name in ("term1","term2","term3") I would like to get the list of terms from a text file with one term per line. How can I do this? What about if this list of terms will get large? 10K, for example. Will this be efficient or I should use another approach? May be temporary table an...

Can someone recommend a good tutorial on MySQL indexes, specifically when used in an order by clause during a join?

I could try to post and explain the exact query I'm trying to run, but I'm going by the old adage of, "give a man a fish and he'll eat for a day, teach a man to fish and he'll eat for the rest of his life." SQL optimization seems to be very query-specific, and even if you could solve this one particular query for me, I'm going to have to...

MySQL LEFT JOIN issue with three WHERE statements

I am building a note taking app for myself with tag filtering functions, but am having an issue when trying to grab notes with the tags. The tag filter needs to use AND not IN, because it will help better narrow down what I am looking for. My tables are configured like this: + notes note_id | note_title | note_uid + tags...

Common type for generic classes of different types

I have (for example) Dictionary of different generic types (d1, d2, d3, d4) and I want to store them in something var d1 = new Dictionary<int, string>(); var d2 = new Dictionary<int, long>(); var d3 = new Dictionary<DateTime, bool>(); var d4 = new Dictionary<string, object>(); var something = ?...

Dynamic Linq help, different errors depending on object passed as parameter?

I have an entityDao that is inherbited by everyone of my objectDaos. I am using Dynamic Linq and trying to get some generic queries to work. I have the following code in my generic method in my EntityDao : public abstract class EntityDao<ImplementationType> where ImplementationType : Entity { public ImplementationType getOneByValu...

Using normalize-string XPath function from SQL XML query ?

Hi, is it possible to run an SQL query, with an XPath "where" clause, and to trim trailing spaces before the comparison ? I have an SQL XML column, in which I have XML nodes with attributes which contain trailing spaces. I would like to find a given record, which has a specified attribute value - without the trailing spaces. When I tr...

How do I convert this Crystal Report IF statement for use in a WHERE clause in Reporting Services?

I'm trying to translate this Crystal Reports IF Statement for use in a WHERE clause - {@receipt_datetime_daylight} in {?DateRange} and (if {?Call Sign} = "All Call Signs" Then {cacs_incident_task.resource_or_class_id} = {cacs_incident_task.resource_or_class_id} Else If {?Call Sign} = "All Sierra Call Signs" Then {cacs_incident_t...

How can I select values from different rows depending on the most recent entry date, all for the same employee ID?

Basically I have a table which is used to hold employee work wear details. It is formed of the columns: EmployeeID, CostCentre, AssociateLevel, IssueDate, TrouserSize, TrouserLength, TopSize & ShoeSize. An employee can be assigned a pair of trousers, a top and shoes at the same time or only one or two pieces of clothing. As we all k...

SQL: How can i update a value on a column only if that value is null?

Hey, I have an SQL question which may be basic to some but is confusing me. Here is an example of column names for a table 'Person': PersonalID, FirstName, LastName, Car, HairColour, FavDrink, FavFood Let's say that I input the row: 121312, Rayna, Pieterson, BMW123d, Brown, NULL, NULL Now I want to update the values for this person, b...

Select all rows where a varchar column converts to a decimal

I have a varchar column that has generally has a decimal value, but some times there is some garbage text characters in that field. Is it possible to filter in the WHERE clause for rows that sucessfully convert to a decimal value? I am using sql-server 2005 ...

In SQL / MySQL, what is the difference between "ON" and "WHERE" in a join statement?

The following statements give the same result (one is using "on", and the other using "where"): mysql> select * from gifts INNER JOIN sentGifts ON gifts.giftID = sentGifts.giftID; mysql> select * from gifts INNER JOIN sentGifts WHERE gifts.giftID = sentGifts.giftID; I can only see in a case of a Left Outer Join finding the "unmatched"...

LINQ Where clause problem

Hi again, When trying to do a query using LINQ in VB.net in order to select some employees of a datatable previously filled with a dataset I have a problem when using where clause. What I want is select all the employees of the datatable except those that appear in a list of excluded employees named CurrentExcludedEmployeesLst. So I fol...

Is this Where condition in Linq-to-sql join correct?

I have the following Iqueryable method to show details of a singl material, public IQueryable<Materials> GetMaterial(int id) { return from m in db.Materials join Mt in db.MeasurementTypes on m.MeasurementTypeId equals Mt.Id where m.Mat_id equals id select new Materials() { ...

Codeigniter setting multiple where conditions, how to unset one

I've got a script that is a notify_url from paypal that is supposed to update multiple tables in my database using the following code: //update first table $this->db->where('someid', $somid); $this->db->update('table', $data); ///update second table $this->db->where('somesecondid', $somesecondid) $this->db->update('anothertable', $dat...

if-else equivalent via linq-to-sql query in where clause c#

Hi guys, Basically I wanted to have an if-else statement in my linq to sql statement. var query = from d in database if(x == y) { where d.Attr = x } else { where d.Attr = y } select d; Any ideas? ...

Check username and password in LINQ query

this linq query var users = from u in context.Users where u.UserEMailAdresses.Any(e1 => e1.EMailAddress == userEMail) && u.UserPasswords.Any(e2 => e2.PasswordSaltedHash == passwordSaltedHash) select u; return users.Count(); returns: 1 even when there is nothing in password table. how come? what i am trying to...

Beginner SQL section: avoiding repeated expression

I'm entirely new at SQL, but let's say that on the StackExchange Data Explorer, I just want to list the top 15 users by reputation, and I wrote something like this: SELECT TOP 15 DisplayName, Id, Reputation, Reputation/1000 As RepInK FROM Users WHERE RepInK > 10 ORDER BY Reputation DESC Currently this gives an Error: Invalid col...

C# Generics Multiple Inheritance Problem

Can any one help me with this syntax issue with C#? I have no idea how to do it. class SomeClass<T> : SomeOtherClass<T> where T : ISomeInterface , IAnotherInterface { ... } I want SomeClass to inherit from SomeOtherClass and IAnotherInterface and for T to inherit ISomeInterface only It seems the problem is that the where keyword scre...

Is this sql where statement correct?

I have 10 rows for today's date but my select statement based on date dosen't seem to work.... SELECT Id,WirelessId,RegNo,DriverName1,MobileNo1,DriverName2,MobileNo1 from DailySchedule where IsDeleted=0 and CreatedDate='2010-05-28' Any suggestion... ...

Dynamic where clause using Linq to SQL in a join query in a MVC application

Dear .Net Linq experts, I am looking for a way to query for products in a catalog using filters on properties which have been assigned to the product based on the category to which the product belongs. So I have the following entities involved: Products -Id -CategoryId Categories [Id] Properties [Id, CategoryId] PropertyValues ...