sql

SQL: Batch update table based on MIN of another? Probably easy, but stuck.

I think this will be easy but I can't see how to do it! I want to update the Author Table, so that the Earliest field contains the year of the earliest book. I need to use MIN somehow but how to update them all with one query? BookTable (BookID, AuthorFK, Year) 01 34 1943 02 34 1933 03 99 1910 04 62 1990 05 99 1901 AuthorTable (Autho...

How can I avoid runtime error 3075 when running this Access VBA query?

Can someone tell what is wrong with this query? sqltext = "SELECT utyp, count(*) AS anzahl INTO UTYP_Anzahl FROM 01_umwelt WHERE [01_umwelt].status = Me.Controls(""STATUS"").Value GROUP BY utyp;" I am getting run time error 3075. ...

Using Triggers To Enforce Constraints

I'm trying to enforce integrity on a table in a way which I do not think a Constraint can... CREATE TABLE myData ( id INTEGER IDENTITY(1,1) NOT NULL, fk_item_id INTEGER NOT NULL, valid_from DATETIME NOT NULL, invlaid_from DATETIME NOT NULL ) The constraint I wan...

What's a good front end query tool for SYBASE?

Good morning, I've had enough of the text-only editor. Is there a SQL Management Studio-like sql editor for SYBASE? I can't imagine people work with the text editor -- makes me want to pull my teeth out. I couldn't get SQL Management Studio to work with it. No dice with visual Studio 2008 either. Thanks, -Alan. ...

Counting rows for all tables at once

I'm using SQL Server 2005 and would like to know how I can get a list of all tables with the number of records in each. I know I can get a list of tables using the sys.tables view, but I'm unable to find the count. Thank you ...

Should CONTROL permission be given on a Stored Procedure in SQL Server 2005?

I'm running into an issue where granting EXECUTE permissions on a specific Stored Procedure in SQL Server 2005 is not working. Some of the testers messed around with the permissions - and found that if they also granted CONTROL permissions on the Stored Procedure - then it ran fine. They are now convinced that granting CONTROL permissi...

group by query challenge

Hi, I have these tables: customer -------- customer_id int name varchar(255) order ----- order_id int customer_id int discount boolean I can get the number of orders made by each customer with a query like: select c.id, count(o.order_id) from customer c left join order as o using c.customer_id = o.customer_id group by...

What is the best way to check for the existence of a Login in SQL Server 2005.

I am looking for the best way to check that a database login exists in SQL Server 2005. I am currently using IF suser_sid('loginname') IS NOT NULL but suser_sid() returns a value in some cases where a login does not exist. In SQL 2000 we use SELECT * FROM [Master].[dbo].[sysxlogins] WHERE [name] ='loginname' but that table does...

SQL Exclude LIKE items from table

I'm trying to figure out how to exclude items from a select statement from table A using an exclusion list from table B. The catch is that I'm excluding based on the prefix of a field. So a field value maybe "FORD Muffler" and to exclude it from a basic query I would do: SELECT FieldName FROM TableName WHERE UPPER(ColumnName) NOT LIK...

Oracle: What does `(+)` do in a WHERE clause?

Found the following in an Oracle-based application that we're migrating (generalized): SELECT Table1.Category1, Table1.Category2, count(*) as Total, count(Tab2.Stat) AS Stat FROM Table1, Table2 WHERE (Table1.PrimaryKey = Table2.ForeignKey(+)) GROUP BY Table1.Category1, Table1.Category2 What does (+) do in a WHERE claus...

What are the best books for MySQL?

What is the best book for SQL, particularly MySQL? ...

Performance benefit when SQL query is limited vs calling entire row?

How much of a performance benefit is there by selecting only required field in query instead of querying the entire row? For example, if I have a row of 10 fields but only need 5 fields in the display, is it worth querying only those 5? what is the performance benefit with this limitation vs the risk of having to go back and add fields i...

Prevent mutually recursive execution of triggers?

Suppose you have the tables Presentations and Events. When a presentation is saved and contains basic event information, such as location and date, an event will be created automatically using a trigger. (I'm afraid it's impossible for technical reasons to simply keep the data at one place and use a view.) In addition, when changing this...

Linked Server Error using MSDTC

I am Showing An example of Stored Procedure For Data Transaction using "Linked Server" Between Two System Through Internet Alter Proc [dbo].[usp_Select_TransferingDatasFromServerCheckingforExample] @RserverName varchar(100), ----- Server Name @RUserid Varchar(100), ----- server user id @RPass Varchar(100), ---...

What is the best way to generate ranks in MYSQL?

What's the best way to get the rank of the rows in addition to the row data in MYSQL? For instance, say I have a list of students and I want to rank on the GPA. I know I can order by the GPA, but what's the quickest way to have MYSQL return the rank as well in the rowdata I get back? ...

Better way of returning the values of a column in Active Record?

Quick one, but thought I'd ask. Is there a better way of getting the column values from a model's column than something like this? Item.count(:all, :group => 'status').reject! { |i, e| i.blank? }.collect { |i,e| i} ...

Determine if table exists in SQL Server CE?

I know this is similar to this question, but I'm using SQL Server CE 3.5 with a WinForms project in C#. How can I determine whether a table exists? I know the IF keyword is not supported, though EXISTS is. Does information_schema exist in CE where I can query against it? Thanks. ...

Best database independent SQL DDL utility?

I'm working on a project for the .net platform and would like to support multiple database types. I would like to keep the DDL under source control in a generic format then convert it to database specific DDL for deployment. So I'm looking for utilities that will convert generic DDL into database specific DDL. Ideally it would support M...

Vertical to Horizontal?

Hi again, I have a PostgreSQL table that looks like this: A -> B A -> C A -> G A -> H B -> O B -> K Where "->" separates two columns where the first points to the second (hyperlinks). Now I would like to take all distinct values in the first column and assign them an ARRAY containing all values to which they point to in the second co...

MySQL IN() question

I have a numerical field called category_id in my table. I want to do something like this. $ids=implode(',',$id_array); $sql="SELECT * FROM myTbl WHERE IN(category_id,'$ids')"; Which should output something like: SELECT * FROM myTbl WHERE IN(category_id,'1,2,3,4,5,6'); Is this possible and am I using the right syntax for this? Is ...