We're using a "1 audit table for each monitored Table" design; However, in our case emp(PARENT) table has a child table emp_address which also needs to be monitored, so we have emp_audit and emp_addr_audit tables. postgres audit SQL : how to join PARENT and CHILD tables for reporting purposes. /* Employee table */ create table emp...
So in rails I use sqlite3 for development and mysql for production. Sqlite3 and mysql handle booleans differently ("t" or "f" in sqlite3 and true or false on mysql). Usually it's not a problem when I do a find because I can do this: Comment.find(:all, :conditions => ["viewed = ?", false]) And rails will insert the appropriate value de...
I want to do the following pseudo-SQL: SUM( SELECT a FROM tab WHERE b > 0); This syntax doesn't work (at least not in sqlite), so I'm trying to figure out the proper phrasing. SELECT SUM(a) FROM tab WHERE b > 0 GROUP BY (*); % nope SELECT SUM(a) FROM tab WHERE b > 0 GROUP BY (1); % nope Suggestions? ...
Just curious about SQL syntax. So if I have SELECT itemName as ItemName, substring(itemName, 1,1) as FirstLetter, Count(itemName) FROM table1 GROUP BY itemName, FirstLetter This would be incorrect because GROUP BY itemName, FirstLetter really should be GROUP BY itemName, substring(itemName, 1,1) But why can't we simply us...
If you are talking about btrees, I wouldn't imagine that the additional overhead of a non clustered index (not counting stuff like full text search or other kind of string indexing) is even measurable, except for an extremely high volume high write scenario. What kind of overhead are we actually talking about? Why would it be a bad ide...
Joins are usually used to fetch data from 2 tables using a common factor from either tables Is it possible to use a join statement using a table and results of another SQL statement and if it is what is the syntax ...
General Case How do you perform a left join across a many-to-many relationship when you want to add a condition to the foreign side of the relation? Specific Case We're dealing with two tables: team and pool. There is also a team_pool table serving as a many-to-many junction table between them. Additionally, a pool has a stage_id colu...
So lets say I have a table that looks something like this: ItemName ProductType ---------------------------------------------------------- Name1 Type1 Name2 Type1 Name3 Type1 Name4 Type2 Name5 Type3 and so on for thousands of recor...
I have a timestamp field [SubmissionDate], which defaults to current_timestamp, and i was wondering how do i query my database in such a fashion that for example i get only shown all entries submitted on a certain year and month? Something like: SELECT * FROM DNA_entrys WHERE `SubmissionDate`.month = February AND `SubmissionDate`.year =...
I have a column in a table of +15000 entries - I want to duplicate that entire column while retaining it's values and name it differently. I'm using Microsoft SQL Server Management Studio. How do I go about that? Thanks. ...
Is there any possibility to test T-SQL code on the internet? I mean that e.g many programming languages like Python has several online shell interpreters on the internet. Is there one for T-SQL? ...
hello guys, I want to know the best way of testing data access functionality. I know that it is possible to create mocks to change data layer objects that is using to test business logic. However is it possible to test if sql queries to database are correct. Scenario: A method must return employees which were applied for work last mont...
I have a query that goes something like this : ;WITH t as ( select 1 as RowNumber, 1 as ObjectID, 10 as [Col1], 20 as [Col2], 20 as [Col3], 20 as [Col4] UNION ALL select 2 as RowNumber, 2 as ObjectID, 20 as [Col1], 30 as [Col2], 40 as [Col3], 50 as [Col4] ) SELECT RowNumber, ObjectID, ( SELECT MAX(Amount) ...
Very basic, but didn't know what this type of query is called...I want a query that will basically do this (pseudo-SQL): SELECT name FROM Table WHERE activity_status != 'active' AND there are no rows with that same name where activity_status = 'active' (in other words, return names of the inactive only when not one with t...
I am trying to write a classifier function for the SQL 2008 resource governor. I would like to use a user created database role to identify if the user should go into a particular work load group. The logins in question are SQL logins. I can not use IS_MEMBER(), because IS_MEMBER restricts itself to the current database context (in th...
Suppose I have a 2 column table with columns (arg, value) and a user-defined function foo. Is there a way to have an update query that goes through the table and calls foo with argument arg and sticks the results in column value for every row in the table? ...
I have a stored procedure that will INSERT a record into a table. I have created a unique key constraint on the table to avoid duplicate records. Regardless of the fact that there is a unique key constraint defined, I am using an IF() condition (prior to the INSERT statement) to check for a duplicate record that may already exist. How...
Hello, I have table (MyISAM) with columns views_total, views_24h, views_7d, views_30d. views_24h = view for last 24 hours, views_7d = last 7 days etc. All this columns are INT and INDEX because I'm doing sorting by them. Every hour (for 24h), or every day (for 7d, 30d) I'm updating this columns using UPDATE ... JOIN (... UNION ALL ...)...
I have a table as follows: colA colB(unique) colC --------------------------------- 1 1449 0.50000000 1 1451 1.03400000 1 2404 5.98750000 1 1454 6.00000000 3 1465 1.40000000 3 1467 1.56000000 3 1476 3.00000000 3 1469 ...
I have a table with fields: id name parent_id grandparent_id I want to select the id, name, parent name, grandparent name. Can I do this with a self join? I basically want to retrieve the "name" value where parent_id = id and return one row. Example: id name parent_id grandparent_id ---------------------------------...