The ON clause defines the relationship between the tables. The WHERE clause describes which rows you are interested in. Many times you can swap them and still get the same result, however this is not always the case with a left outer join. If the ON clause fails you still get a row with columns … Read more
In the default configuration of a SQL Server database, string comparisons are case-insensitive. If your database overrides this setting (through the use of an alternate collation), then you’ll need to specify what sort of collation to use in your query. SELECT * FROM myTable WHERE myField = ‘sOmeVal’ COLLATE SQL_Latin1_General_CP1_CI_AS Note that the collation I … Read more
SELECT id, name FROM mytable WHERE name IN (‘B’, ‘A’, ‘D’, ‘E’, ‘C’) ORDER BY FIELD(name, ‘B’, ‘A’, ‘D’, ‘E’, ‘C’) The FIELD function returns the position of the first string in the remaining list of strings. However, it is much better performance-wise to have an indexed column that represents your sort order, and then … Read more
Use LOWER function to convert the strings to lower case before comparing. Try this: SELECT id FROM groups WHERE LOWER(name)=LOWER(‘Administrator’)
You can certainly do it within a Where clause (extension method). If you need to build a complex query dynamically, though, you can use a PredicateBuilder. var query = collection.Where( c => c.A == 1 || c.B == 2 ); Or using a PredicateBuilder var predicate = PredicateBuilder.False<Foo>(); predicate = predicate.Or( f => f.A == … Read more
No, that order doesn’t matter (or at least: shouldn’t matter). Any decent query optimizer will look at all the parts of the WHERE clause and figure out the most efficient way to satisfy that query. I know the SQL Server query optimizer will pick a suitable index – no matter which order you have your … Read more
You need to move the WHERE filter to the JOIN condition: SELECT c.Customer, c.State, e.Entry FROM Customer c LEFT JOIN Entry e ON c.Customer=e.Customer AND e.Category=’D’ See SQL Fiddle with Demo
You can use Regular Expression too… it would be like: SELECT * FROM myTable WHERE col1 REGEXP ‘^[0-9]+$’; Reference: http://dev.mysql.com/doc/refman/5.1/en/regexp.html
You need to put it in the join clause, not the where: SELECT * FROM categories LEFT JOIN user_category_subscriptions ON user_category_subscriptions.category_id = categories.category_id and user_category_subscriptions.user_id =1 See, with an inner join, putting a clause in the join or the where is equivalent. However, with an outer join, they are vastly different. As a join condition, … Read more
HAVING: is used to check conditions after the aggregation takes place. WHERE: is used to check conditions before the aggregation takes place. This code: select City, CNT=Count(1) From Address Where State=”MA” Group By City Gives you a table of all cities in MA and the number of addresses in each city. This code: select City, … Read more