sql

Fetching multiple rows based on grouping

Consider this data PK field1 field2 1 a b 2 a (null) 3 x y 4 x z 5 q w I need to get this data select all columns from all rows where field1 has count >1 Which means the desired output is PK field1 field2 1 a b 2 a (null) 3 x y 4 x z i tried and finally settled for select ...

Restricting deletion with NHibernate

I'm using NHibernate (fluent) to access an old third-party database with a bunch of tables, that are not related in any explicit way. That is a child tables does have parentID columns which contains the primary key of the parent table, but there are no foreign key relations ensuring these relations. Ideally I would like to add some forei...

mysql statement with nested SELECT - how to improve performance

This statement appears inefficient because only one one out of 10 records are selected and only 1 of 100 entries contain comments. What can I do to improve it ? $query = "SELECT A,B,C, (SELECT COUNT(*) FROM comments WHERE comments.nid = header_file.nid) as my_comment_count FROM header_file Where A = 'admin' " edit: ...

PHP/MySQL - updateing 2 tables in one request

Morning, I want to learn more about sql and I'm wanting to update to tables; $query3 = "INSERT INTO `$table1`, `$table2` ($table1.DISPLAY_NAME, $table1.EMAIL_ACCOUNT, $table2.DISPLAY_NAME, $table2.EMAIL_ACCOUNT) values ('" . DISPLAY_NAME . "', '" . EMAIL_ADDRESS . "', '" . $get['rn'] . "', '" . $email . "')"; could some one point me i...

Combining multiple rows into one row, Oracle

Hi. I'm working with a database which is created in Oracle and used in a GIS-software through SDE. One of my colleuges is going to make some statistics out of this database and I'm not capable of finding a reasonable SQL-query for getting the data. I have two tables, one with registrations and one with registrationdetails. It's a one to...

Inserting variables into a query string - it won't work!

Basically i have a query string that when i hardcode in the catalogue value its fine. when I try adding it via a variable it just doesn't pick it up. This works: Dim WaspConnection As New SqlConnection("Data Source=JURA;Initial Catalog=WaspTrackAsset_NROI;User id=" & ConfigurationManager.AppSettings("WASPDBUserName") & ";Password='" ...

C# SQL Data Adapter Fill on existing typed Dataset

I have an option to choose between local based data storing (xml file) or SQL Server based. I already created a long time ago a typed dataset for my application to save data local in the xml file. Now, I have a bool that changes between Server based version and local version. If true my application get the data from the SQL Server. I'm...

SQL 2000 - Returning from a Stored Procedure

Hello, I'm writing a stored procedure. This procedure has a case where if it is met, I want to stop executing the procedure and return -1. How do I do this? Currently, I'm trying the following: IF @result <> 1 BEGIN SELECT -1 END However, SELECT is not a typical "return". As you can imagine I spend most of my time ...

DB2 CASE Statement

I need to somehow use the CASE syntax (which is beyond me) to affect the database results based on criteria. I have a bunch of royalties in 0.# form (royalty) I have a title ID # (title_id) and I need to show the new increase in royalties so that I can use the data. IF: they have a current royalty of 0.0 - 0.1 = 10% raise IF: th...

Creating an ER diagram for hsqldb that exports SQL

Hi fellows! I'm currently designing a DB scheme. I have to use hsqldb. - Normally I like creating an ER diagram, and I export the SQL source. Therefore I'm looking for a tool that automatically transforms my ER diagram into hsqldb sql code. Using MySQL Workbench or some sort of web interface (http://ondras.zarovi.cz/sql/demo/) seems to...

Getting all rows from a Table where the column contains only 0

I got a little problem i need a sql query that gives all rows back that only contains 0 in it. the column is defined as varchar2(6) the values in the column looks like this: Row Value 1 0 2 00 3 00 4 100 5 bc00 6 000000 7 00000 my first solution would be like this: Oracle: subst...

Doctrine_Record doesn't set PK in object after save()

Per documentation, Doctrine_Record after saving should set id of newly created record as object property. In my case, new record is created, but not value is set on object (while database has this new id value). What has caused this? $user1 = new ModelUsers(); $user1->save(); echo "last insert id=" . $user1->UserId; PS UserId is confi...

SQL Server - How to switch between 2 possible SELECT statements

I would like to use a parameter in my mssql stored procedures to switch between a small and a detailed result (for maintainability, performance and network load reasons). If parameter is set to 1 i get all columns, else only the one or two most important. In a very limited way it works like this: ALTER PROCEDURE [dbo].[GetAllUsers] @d...

Combining IN and NOT IN in SQL as single result

I apologize for the vague title. I am attempting to write a query that returns an alias column with matching values (resulting from an IN) as well as an alias column with values that do not match (using NOT IN). I want the result set to have: userId | matches | nonmatches. I currently have the following query which returns the matches as...

SQL Query Count on Column Information

Using MySQL 5 with table "abrechnung" id datum title betrag wal jon al ben 7 2010-02-08 Essen 1362 0 0 1 0 8 2010-02-15 Essen 324 0 0 1 2 9 2010-02-15 Essen 5732 0 0 1 2 10 2010-02-15 Essen 3245 0 2 1 2 What I want in the end is: Each rows "betrag" is divided thro...

filter by multiple attributes

I have the table : id id_products id_atribut name value 1 13 8 autdio 2.1 2 13 9 hdd 200 Gb 3 13 10 cd-rom 2 4 20 8 audio 2.1 the problem is, how can i select from this table where name="audio" and value="2.1" and name="hdd"...

SQL Server copy all rows from one table into another i.e duplicate table

I want to keep a table as history and replace it with an empty one. How can I do this through the management studio? --Jonesy ...

Limit Records fetched in Android (Sqlite database)

Hi. I am trying to fetch sms messages from inbox, sent items and drafts. I would like to do a pagination for my list view for that it's imperative that I fetch records in pages/chunks. I am not familiar with sqlite which is the database I understand android use to store the data. Can someone tell me how can I restrict the number of rec...

How can I force a subscriber to be synchronized from a local snapshot?

Hello, I have a SQL 2005 server replicating(merge\push) to SQL 2005 and SQL 2000 servers. I have multiple subscribers spread througout the United states. I have set , @snapshot_in_defaultfolder = N'false', @alt_snapshot_folder = N'c:\snapshots\Merge\' (sample location). I take the snapshot from the publisher that is in the same locat...

Convert tax like IRS function to SQL

Trying to convert this tax-like IRS function http://stackoverflow.com/questions/1817920/calculating-revenue-share-at-different-tiers to SQL. The assumption is the table would be one column (price) and the tiers will be added dynamically. My first attempt has some case statements that didn't work out well. I have since scrapped it :) Tha...