I have a simple database with few tables (and some sample columns): Posts (ID, Title, Content) Categories (ID, Title) PostCategories (ID, ID_Post, ID_Category) Is there a way to create single SQL query which will return posts with categories that are assigned to each post? ...
Hi Everyone, How can I change the following code to write to the database null instead of empty strings? Public Sub SetChangeRequest(ByVal referenceLeaseID As Integer, _ ByVal referenceCustomerID As Integer, _ Optional ByVal custUnitNum As Object = Nothing, _ ...
I need to be able to change the primary keys in a table. The problem is, some of the keys will be changing to existing key values. E.g. record1.ID 3=>4 and record2.ID 4=>5. I need to keep these as primary keys as they are set as foreign keys (which cascade up update) Is there a reasonable way to accomplish this, or am I attempting sql...
I have some existing code that accepts a java.sql.ResultSet that contains info retrieved from an Oracle database. I would now like to reuse this code, but I'd like to pass it a ResultSet object that I create myself from some in-memory data that is not affiliated with any database. Is there an existing Java framework class that I can us...
I have a table full of Actions. Each Action is done by a certain User at a certain DateTime. So it has 4 fields: Id, UserId, ActionId, and ActionDate. At first, I was just reporting the top 10 most recent Actions like this: (from a in db.Action orderby a.ActionDate descending select a).Take(10); That is simple and it works. But the ...
I am putting together a site made out of two scripts, with users. The 2 scripts have info about the members , they will act as one site so I need the users info to be the same on both. When the user fills in the info on the DB1 they should appear in the DB2 as well. The information fields I would need the same are basic: name, email, ...
Hi everyone, Just want to see if anyone has a better way to accomplish what I need. First a background. I have a table in my database that currently has about 20,000 rows. In the table we have a column for FirstName and LastName. There is also a column that is a ReferenceID. There can be multiple FirstName, LastName combinations for ea...
In SQL Server 2008, I am using triggers to capture all changes made to a specific table in my database. My goal is to capture the entire change. That is, to capture what data is being inserted, not just that data is being inserted. In the trigger I am using the EventInfo column of the result set returned by DBCC INPUTBUFFER to get the...
What is the magical incantation to create an identity column in Oracle? ...
How can I assign alias to tables with SubSonic 2.1? I am trying to reproduce the following query: SELECT * FROM posts P RIGHT OUTER JOIN post_meta X ON P.post_id = X.post_id RIGHT OUTER JOIN post_meta Y ON P.post_id = Y.post_id WHERE X.meta_key = "category" AND X.meta_value = "technology" AND Y.meta_key = "keyword" AND Y.me...
Is this SQL query statement: SELECT p.id, p.[name], SUM(ps.sales_amount) AS GROSS_SALES FROM products p LEFT OUTER JOIN product_sales ps ON p.id = ps.product_id GROUP BY p.id, p.[name] better than: SELECT SUM([t2].[value]) AS [SalesAmount], [t2].[id] AS [ProductId], [t2].[name] AS [ProductName] FROM ( SELECT ( SELECT SU...
I need to create a token to appear on a querystring that can be passed to a web page and decoded in order to find a record in a database view. The token should not be vulnerable to brute force incrementing value type attacks. View records in the database are uniquely defined as a combination of two keys. The generation of this token n...
Looking at a job descriptions where "advanced SQL" is a requirement. I can write basic queries as well as anyone, and have worked with MySQL databases in a professional setting, but what would I be getting into with these jobs, if I were to be hired? What are examples of advanced SQL and where am I along the scale of SQL noob to SQL ma...
let say i have table look like below actionTable ----------- id, user, actionName, time 1 , userX, startdoing, 1/1/2010 9am 2, userX, endDoing, 2/1/2010 10am may i know whether possible to use 1 sql select statement to minute record 2 from 1 and get time spent ? or use hibernate.criteria to do this? ...
I am trying to get a count of each value in a table using the following SQL: SELECT col, COUNT(col) FROM table GROUP BY col (There's a WHERE clause in the real code, but it has no impact). When I run this I get results like so: a - 5 b - 4 <null> - 0 It doesn't matter how many null entries I have, it always shows a coun...
i have the most annoying issue; i have an orchestration doing a lookup against a view. it works in locally and on our development server, but not in QA or UAT. same code. same views. just different environments. to test this and be sure it's not a coding issue i take my local BizTalk, configure the port to Server A, fire a message and ...
I have many complex sql queries that I would like to optimize. The performance on it is pretty bad. Is there an online tool to optize such queries? ...
Is there a way (or existing library, not necessarily built into .NET) in C# that performs simple SQL string validation? Scenario: Building update statement to reduce load on SQL load vs. individual statements. In case the string build does something "odd", like ending with a comma for instance, I'd like to be able to validate that the s...
I'm writing a drupal module, and I need to write a query that returns particular rows of one of my content_type tables. My query so far is: SELECT DISTINCT pb.*, f.filepath FROM {content_type_promo_box} pb LEFT JOIN {files} f ON pb.field_promo_image_fid = f.fid I realized as I was working that the table not only contains each cck fiel...
I have foo.sql as: print 'foo=$(foo)' Then I have in foo.cmd the following shell script: sqlcmd -i foo.sql -v foo="c:\path" Running foo.cmd prints: foo=\path how do I escape the "c:"? Is dos-shell eating it, or is it sqlcmd? ...