where-clause

Filter by count of children using Rails 3

I'd like to select posts that have one or more comments using Rails 3 and a single query. I'm trying something like this: Post.includes(:comments).where('count(comments.id)>0') However I get this error: ActiveRecord::StatementInvalid: PGError: ERROR: aggregates not allowed in WHERE clause I've googled this and similar approaches,...

MySQL user-defined variable in WHERE clause

I want to know if there is a way to use a user-defined variable in WHERE clause, as in this example: SELECT id, location, @id := 10 FROM songs WHERE id = @id This query runs with no errors but doesn't work as expected. Thank you in advance. ...

nest dynamic case when statements in sql 2008

Hello Experts, I have the following code in .net. foreach (string key in EntityNumbers.Keys) { if (EntityNumbers[key] != null) { e = new WebServices.Entity(); e.HierarchyLevel = key; e.Number = EntityNumbers[key]; ...

PHP: Incorrect SQL syntax error

Hi folks, I have a PHP class that creates a SQL query based on values entered from a form. I'm getting Incorrect syntax near the keyword 'WHERE'. ) ) Here is my code. The problem is occurring around each of the WHERE clauses, (already dealing with SQL injections btw). if($from != ''){ $from = date('Y-m-d H:i:s',strtoti...

Using date of type char in where clause

I have a SQL Server database table with a char column named "DATE" (I know, really bad, but I didn't create the database) that has dates stored in this format as strings: YYMMDD. I need to return records between these dates, so treat them as actual dates and I've tried every combination I know but still get errors. Any help is much appre...

count(*) where cond = val, or count(cond = val)

What is the difference between these two methods? select count(*) from `table` where `column` = 'value'; and select count(`column` = 'value') from `table`; To me they seem to do the same thing; I assume this is not the case for the database. Does one method leverage indexes better than the other? In this case I use MySQL but a gen...

SELECT to get two entries, from same table, differentiated by date, in one row

I have a table in which i keep different meters (water meter, electricity meter) and in another table i keep the readings for each meter. The table structure is like this : The meter table MeterID | MeterType | MeterName The readings Table: ReadingID | MeterID | Index | DateOfReading The readings for a meter are read monthly. The t...

SQL procedure where clause to list records

I have a stored procedure that will query and return records based on if items are available or not. That part is easy, Im simply passing in a variable to check where available is yes or no. But what I want to know is how do I throw a everything clause in there (i.e available, not available, or everything)? The where clause right now i...