I have a Rails app with Users, and each user HABTM Roles. I want to select Users without a specific role. I have searchlogic at my disposal, and I'm lost. I've tried using a combination of conditions and joins and includes and what not, but I can't seem to nail it. This works: User.find(:all, :conditions => ['role_id != ?', Role[:admin...
I'm fresh out of designing a set of tables, in which I came up with an architecture that I was very pleased with! I've never seen it anywhere else before, so I'd love to know if I've just reinvented the wheel (most probable), or if this is a genuine innovation. Here's the problem statement: I have Employees who can each sign a differen...
Hi, I was wondering why MYSQL uses a single equals sign in conditional statements instead of the more typical two equals signs. Is there a technical/historical reason for this? Thanks. ...
Hello, I can't seem to find why this function doesn't insert records into the database. :( I get no error messages or whatsoever, just nothing in the database. EDIT: this is how my query looks now .. still nothing .. connection.Open(); XmlNodeList nodeItem = rssDoc.SelectNodes("/edno23/posts/post"); foreach (XmlNode xn in nodeItem)...
I'm bumping into somenull fields in a SQL2005 db. Some report Null and others have values like 1.00 or 713.00. I'd like a bullet proof way to convert the 'Null's to 0 and the '1.00' and '713.00' values into Money types. ...
I am trying to display Month and YY part of the date column such that it should appear as Original Format Apr 12 2009; Feb 20 2009; Dec 24 2008; May 18 2009; Jan 8 2009; Dec 6 2008; Apr 19 2009; Jan 4 2009; May 13 2009; Jan 5 2009 From these dates the day part should be ripped off such that it appears as "Apr 09" ...
Hello Can anyone post a SQL query for Calculating Total No. of Orders per Day? Here are the Columns along with their data in my Database. order_id order_placed_date order_total - 1 12/30/2008 12:06:24 AM 2499.99 - 2 2/3/2009 1:57:17 AM 199.99 - 3 2/3/2009 1:58:27 AM 449.99 - 4 5/3/2009 1:58:48 AM 299.99 ...
Alright, here's an odd one from an MS Access database I'm running. I have a SQL query: SELECT * FROM [Service Schedule] WHERE ID=2 AND Volume <= 3000 AND Term='Monthly' AND special = 'Regular' ORDER BY volume When I put that into the SQL view of the query builder, I get 2 records, one with a volume of 0 and one with a volum...
I'm working on a C# project that uses System.Data.OleDb.OleDbCommand class to create and alter tables in an MS Access DB. I generate the SQL statement, pass it to the object, then call the ExecuteNonQuery function. I was able to figure out the proper MS Access SQL syntax to create columns of the following Access data types: AutoNumbe...
Hi, I would appreciate a little expert help please. in an SQL SELECT statement I am trying to get the last day with data per month for the last year. Example, I am easily able to get the last day of each month and join that to my data table, but the problem is, if the last day of the month does not have data, then there is no returned d...
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...
What's the equivalent to SQL Server's TOP or DB2's FETCH FIRST or mySQL's LIMIT in Progress SQL? ...
Hi all, [Update: The query works if I hardcode in the parameters - so it has to do with the way I am adding parameters to the query] For the life of me, I cannot figure out what the problem is here. Here is the query being passed to the datareader: SELECT * FROM (SELECT TOP ? StartDate, [ID] FROM (SELECT TOP ? StartDate, [ID]...
I am in the midst of refactoring my database to support an upgrade to our web-based tool. Imagine that I have four tables: Milestones, Categories, Skills, and PayRates. Under the old schema, each of these tables only listed a name, and that name was the key for the table. Under the new schema, each table has not only a name, but also ...
Ok, got a tricky one here... If my data looks like this: Table1 ID Date_Created 1 1/1/2009 2 1/3/2009 3 1/5/2009 4 1/10/2009 5 1/15/2009 6 1/16/2009 How do I get the records that are 2 days apart from each other? My end result set should be rows 1-3, and 5-6. Thanks! ...
I have recently be running into many different areas of sql server that I normally don't mess with. One of them that has me confused is the area of Logins and Users. Seems like it should be a pretty simple topic... It appears that each login can only have 1 user and each user can only have 1 login. A login can be associated to multipl...
Hy guys, can anybody please help me with a subquery in Oracle database 10g? I need to extract the values for a column in the first table as value of another column in the second table. I currently use this statement: SELECT CASE WHEN A.column1 = 'A' THEN 'aaa' WHEN A.column1 = 'B' THEN 'bbb' ....... WHEN A.column1 = 'X' THEN 'xxx' ELS...
I am wanting to have a view table that aggregates rows from four tables. The first primary table is client and is just a primary key unique id and a profile_id. The second table, profile, holds all of the firstname, address, zipcode kind of information. The third table is email; there can be many email objects to one client object but t...
I have a question reagrding Oracle materialized views ... We have two databases: Core Database Reporting database The reporting database has: a database link to the Core database a number of synonyms to the tables in the Core database a number of Materialized views defined on top of those synonyms. The views are set up to refres...
I have the tables Patient, Service, PatientStatus, Status - a Patient can have multiple statuses discriminated by a Service. I want to build a view that shows for each service and each patient what their current status is, even if they don't have a status for that service. I've got some SQL that does this, but can I write it better? (...