where-clause

Dynamic where clause (OR) in Linq to Entities

With LINQKit’s PredicateBuilder you can build predicates dynamically. var query = from u in context.Users select u; var pred = Predicate.False<User>(); if (type.HasFlag(IdentifierType.Username)) pred = pred.Or(u => u.Username == identifier); if (type.HasFlag(IdentifierType.Windows)) pred = pred.Or((u => u.WindowsUsername == identifier); return query.Where(pred.Expand()).FirstOrDefault(); // or return query.AsExpandable().Where(pred).FirstOrDefault(); This is what the Expand is for: Entity Framework’s query … Read more

Left Join With Where Clause

The where clause is filtering away rows where the left join doesn’t succeed. Move it to the join: SELECT `settings`.*, `character_settings`.`value` FROM `settings` LEFT JOIN `character_settings` ON `character_settings`.`setting_id` = `settings`.`id` AND `character_settings`.`character_id` = ‘1’

Conditional WHERE clause in SQL Server

Try this SELECT DateAppr, TimeAppr, TAT, LaserLTR, Permit, LtrPrinter, JobName, JobNumber, JobDesc, ActQty, (ActQty-LtrPrinted) AS L, (ActQty-QtyInserted) AS M, ((ActQty-LtrPrinted)-(ActQty-QtyInserted)) AS N FROM [test].[dbo].[MM] WHERE DateDropped = 0 AND ( (ISNULL(@JobsOnHold, 0) = 1 AND DateAppr >= 0) OR (ISNULL(@JobsOnHold, 0) != 1 AND DateAppr != 0) ) You can read more about conditional WHERE … Read more

WHERE vs HAVING

Why is it that you need to place columns you create yourself (for example “select 1 as number”) after HAVING and not WHERE in MySQL? WHERE is applied before GROUP BY, HAVING is applied after (and can filter on aggregates). In general, you can reference aliases in neither of these clauses, but MySQL allows referencing … Read more

SELECTING with multiple WHERE conditions on same column

You can either use GROUP BY and HAVING COUNT(*) = _: SELECT contact_id FROM your_table WHERE flag IN (‘Volunteer’, ‘Uploaded’, …) GROUP BY contact_id HAVING COUNT(*) = 2 — // must match number in the WHERE flag IN (…) list (assuming contact_id, flag is unique). Or use joins: SELECT T1.contact_id FROM your_table T1 JOIN your_table … Read more

SQL – HAVING vs. WHERE

WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows. Your query calls for a second kind of condition (i.e. a condition on an aggregation) hence HAVING works … Read more

SQL JOIN – WHERE clause vs. ON clause

They are not the same thing. Consider these queries: SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID WHERE Orders.ID = 12345 and SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID AND Orders.ID = 12345 The first will return an order and its lines, if any, for order number 12345. The second will return … Read more