It's been a crappy Monday AM and I can't think straight. Can someone help me figure out how to group/sum the rows returned so that there is only ONE instance of the AssessorParcelNumber? So, instead of the following result set: 140-31-715-164 3545 2004-09-14 00:00:00.000 1665.00 0.00 0.00 1665.00 140-31-715-164 3545 2004...
I am aware of Oracle's create table syntax CREATE TABLE MyTable( id int primary key, ... ); This will create a table called MyTable with an int primary key. So, nothing new here. but I am having difficulties understanding the following query: CREATE TABLE departament ( cod_dept INTEGER CONSTRAINT dept_key PRIMARY KEY, ...
I have to do checks between a development and release database and do this manually, which is both slow and not 100% reliable (I only visually inspect the tables). Is there a quick and easy way to compare table schemas automatically? Maybe even a feature that does this built right into SQL server? Edit: I'm comparing structure only, t...
I have table with 2 columns .... id id2 1 1 1 2 1 3 2 1 2 2 2 4 3 2 3 3 3 4 I want to return the ids which have for example id2 in (1, 2, 4) but that has all of the values in the list. In this above case it would return id = 2. Is this possible? ...
I am working on EF with C# and WPF as front end. I need to provide a UI for so user can create his own query and get the result. The UI will be list of tables and list of columns to select from (Not happy with UI. Need to improve but new ticks in my mind). So my question is how create, merge(existing query) and execute the queries. The...
I have a database containing two tables - one, states, contains info about each state's income taxes, and the second, brackets, contains the tax brackets for each state connected to States by a numeric key. I would like to use SQL to retrieve each state and the AVERAGE tax bracket amount for each state to output in a recordset. For exam...
I am making a little library(DLL) to manage users and their roles/privileges. The plan is to be able to add this dll to an MVC project and be able to manipulate users/roles/etc. All the data resides in a SQL db. I am using entity framework for data access. So when I initialize a new RoleManager(this is the name of the main class in t...
I am trying to convert a query to return as xml in the correct format The query is simply SELECT * FROM [TABLENAME], and yes it has to be * as this is used for a dynamic utility that needs to work on any table. Anyhow, I want the resulting records to be nested two levels deep like this <Root> <RecordParent> <Record> ...
I have a grails project that is throwing the following exception: org.springframework.dao.DataIntegrityViolationException: could not delete: [Role#4]; SQL [delete from role where id=? an d version=?]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not del ete: [Role#4] In my Role dom...
Hi all, Getting a problem in writing an sql query. two tables: 1st: created patient table 2nd: already created doc table patientid patientname docid workstatus docid docname 1 aaa 2 10 1 ggg 2 bbb 2 20 ...
Hello, I am developing a small user application in Rails 3 and is stuck on a search feature. I got a table called Profile which holds Firstname and Lastname. When searching this table I use: Profile.find_all_by_firstname(params[:keyword]) The thing is that I want the user to be able to search a table that holds contacts (profile_id a...
I have a test suite that needs to delete any existing data in my local MySQL test instance for a large number (but not all) of the tables in the system. delete from customer; Of course, customer has quite a few foreign keys to it, so I also have to delete a few more tables... delete from customer_email; delete from customer_phone;...
SELECT * FROM openorders_tracking WHERE id NOT IN (SELECT tracking_id FROM openorders_commission) SELECT * FROM openorders_tracking LEFT JOIN openorders_commission ON openorders_tracking.id=openorders_commission.tracking_id WHERE openorders_commission.id IS NULL I'm wondering both specifically for this query, and in general if...
I want to create a view in which I select something like the following: select id, name, 1 as active from users However, I want the active field, which I am creating in the select statement (it doesn't exist in the table), to be a bit field. Is there a way to do this? ...
Ok, here is a strange one. I have a routine that goes through and makes several deletions of old data. Ive had a few people complain about getting the error: System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint Looking at my code (im using L2S), i dont see how this is possible. But ok,...
Hello, In my SQL 2008 database table, I have one column name AUTHOR that contains XML data. The XML is not well formed and has data like below <Author> <ID>172-32-1176</ID> <LastName>White</LastName> <FirstName>Johnson</FirstName> <Address> <Street>10932 Bigge Rd.</Street> <City>Menlo Park</City> <State>CA</State> </Address> </Author> ...
Well met! I'm missing something about VS2010... I want to create a solution that let's me use the visual query designer to build queries against an SQL server, as I can when I right click the server in Server Explorer and select New Query. However, I can't seem to save this sort of query. If I go into Solution Explorer, I can create a...
Hi all, plz help with one issue. I have Membership configured with IIS7, tables for it located in my own database, created with aspnet_regsql utility, and I am using custom connection string to access it. This is part of web.config related to Membership : <connectionStrings> <add connectionString="Server=CORESERVER\SQLExpress;Data...
I am using SQL Server 2005... I want to monitor table1 with a trigger for an update. On the occurrence of this update, I will check another table (table2), using the data from table2 I place an item in the queue to execute at a variable date in the future. Could be 10 seconds from now, or 2 hours, this date is determined from checking...
Err I apologize for the title, but I forgot what its actually called in SQL lingo. Thing is using this query: SELECT DISTINCT a.col1, a.col2,a.col3,c.col1, c.col2 FROM table1 a LEFT JOIN table2 c ON a.col1 = c.col3 WHERE a.col2 = '038CQH' I get a result with around 56000 rows, but when I checked using: SELECT a.col1, a.col2,a.c...