sql

Match only if one field matches multiple values (err?)

I've got a mental block. I'm pretty certain this is a dead simple noob question to solve, but I'm drawing a blank: I have a tagging system for articles. This is done by having a separate table that contains an article ID as well as a tag ID, so multiple tags can get assigned to one article and vice-versa. It all works well. But now what...

MySQL - Is it possible to get 'the difference' of two query results?

I need to merge two query results as in union, but I want to only keep the difference between the two results... Is this possible? I'm basically selecting ALL resources in Query 1, and NOT-ALLOWED resources in Query 2, I obviously need the ALLOWED resources in my last result... In pseodo-code: Query1 - Query2 Queryresult 1: +-----...

Looking for an PL/SQL library for 2D grid processing (each row represents one cell).

Hi, I am going to create an app for managing grid-like model - cells at [X,Y] coordinates. This model will be persisted in a RDBMS. I want to keep much of the processing right in the DB server. So I am looking for an PL/SQL library for 2D grid processing (each row represents one cell). It should know to do algorithmic things like s...

Any other way to make the query shorter?

select A.regYear, A.regMonth, isnull(A.regCount,0), isnull(B.regCount,0), isnull(C.regCount,0), isnull(D.regCount,0), isnull(E.regCount,0), isnull(F.regCount,0), isnull(G.regCount,0),isnull(H.regCount,0),isnull(I.regCount,0),isnull(J.regCount,0), isnull(K.regCount,0),isnull(L.regCount,0),isnull(M.regCount,0),isnull(N.regCount,0), isnull(...

(SQL) How would I return the result set from multiple executed stored procedures?

Okay I have a queswtion about returning the recordset from multiple stored procedures. If I have a sproc which does this: exec sproc1 exec sproc2 exec sproc3 Each sproc returns just one number, so it return [no column name] 1500 [no column name] 18000 [no column name] 1253 Obviously a datareader won't be able to handle this, or a...

Infinitely Deep Recursive Children

I have a single-table with the following (relative) structure: foo_id, parent_foo_id, foo_name I would like to build an (effectively) infinite-depth recursive array with these entities, and consequently output them into a tree menu. Nodes with no "parent_foo_id" would be considered at the top of the heirerchy, while all successive n...

How can I replace a value within a Sql Select which returns multiple rows?

I have a table that has default values in a few columns. When I run a stored procedure to select the data for display I want to replace the default values with display friendly values. How can I replace the default values before returning from the stored procedure? ...

Does not Recognize Column in Where Clause when Joining Tables

SELECT * FROM a JOIN (SELECT * FROM b WHERE b.aId = a.Id) AS c ON c.aId = a.Id It says does not recognize: a.Id in the Where Clause. I know its probably cause im using a temp table and a.Id cannot be passed through but is there any way we can do that? Because here is what actually happens SELECT * FROM a JOIN (SELECT * FROM...

How to do 2 update statements at a time?

update accounts set type=1 where id=2; update accounts set type=3 where id=4; ...

MS Access Rounding Precision With Group By

Why doesn't the average of the score of an employee of each month, when summed, equal the average of the employees score (ever)? Average SELECT Avg(r.score) AS rawScore FROM (ET INNER JOIN Employee AS e ON ET.employeeId = e.id) INNER JOIN (Employee AS a INNER JOIN Review AS r ON a.id = r.employeeId) ON ET.id = r.ETId WHERE (((e.id)=@em...

Modifying an Oracle Ref Cursor

Given: Oracle 10.2g is the database I have a table called emp. emp has a VARCHAR2 column called SECRET. SECRET might contain a plaintext string, or it might contain an encrypted string, but I can distinguish one from the other A function called DECRYPT already exists that, given the encrypted string, will return an unencrypted string. H...

creating sql diagram

I installed SQL Server 2005, and I want to create a database but I can't. When I right click on the Database Diagrams folder for my database in SQL Server Management Studio 2005, I don't see any choice for creating database diagram. I see two things: Working With sql server 2000 Diagrams Refresh ...

In MySQL, how to keep a column value not less then 0 ?

for some reason, I want the logic resident in MySQL, not in PHP. ...

MySQL can't see column in PHP. Ok as SQL statement.

Im trying use the following insert: mysql_query ("INSERT INTO users (company_name, fname, lname, salt, email, date_added, password) VALUES ('$CompanyName', '$fname', '$lname', '$salt', '$email', '$mysqldate', '$encrypted')") or die(mysql_error()); But I get an error: Unknown column 'company_name' in 'field list' ...

How to access Seconds_Behind_Master from SQL

I would like to have access to Seconds_Behind_Master (as returned by SHOW SLAVE STATUS) from inside a stored procedure, but can't figure out how to get its value inside a variable. None of the usual SET/SELECT syntax seems to work. Is there a way to do that? ...

Linq doesn't recognize changes in the database

Hi, I did not find something simular so i have to ask: I use linq to SQL and all was fine until i started to use Stored Procedures to update the database entries. (My Stored Proc is something like update all entries which groupid x) The Update runs fine and the values in the database change. But the DataContext ignores this changes. ...

MySQL join with sort on datetime in joined table

I have 2 large mysql tables: Articles and ArticleTopics. I want to query the DB and retrieve the last 30 articles published for a given topicID. My current query is rather slow. Any ideas on how to improve it? More details: The tables: Articles (~1 million rows) +-----------+--------------+------+-----+---------+----------------+ | Fi...

Auto trim Database entries?

Is it possible edit a table definition to automagically trim entries of all trailing whitespace when they are entered? Or would this be more efficient/easier/normally done in the code befeore submitting the entries?? ...

How to limit fetching of entities from SQL query.

SQLQuery query = session.createSQLQuery("select {o.id} from order o " + "LEFT JOIN bookings b ON b.id = o.bookingId " + ); List pusList = query.addEntity(Order.class) .list(); and in order class I have: @OneToOne(cascade = CascadeType.ALL, mappedBy = "order", fetch = FetchType.LAZY) private Trip trip; but during exec...

What are SSIS packages deployment and change-tracking best practices ?

I've got quite a robust development environment regarding my SQL database Schema and Data (everything is source controlled, deployment is automated, etc), but when it comes to SSIS packages, the process is quite tedious and totally manual. How can I automate the SSIS package upgrade process (right now, I copy my packages manually to ea...