sql

How to delete FK one to many?

Hello, I have a table (aspnet_Membership) specifically that has spam records in it that I need to delete. There are some foreign keys and I'm trying to write a simple SQL statement to remove the FK's so I can delete the primary record. So theres a table called 'aspnet_UsersInRoles' that has UserID and RoleID, and in my 'aspnet_Membersh...

Are all SQL Geospatial implementations database specific?

My team is looking into geospatial features offered by different database platforms. Are all of the implementations database specific, or is there a ANSI SQL standard, or similar type of standard, which is being offered, or will be offered in the future? I ask, because I would like the implemented code to be as database agnostic as pos...

How can I improve this Mailing Address SQL Server SELECT Statement?

How can I format a Mailing Address so that I always push all non-null rows to the top? That is, I want to convert an address from the structure below to a mailing address. Here is the structure: [Line1] [varchar](50) NULL, [Line2] [varchar](50) NULL, [Line3] [varchar](50) NULL, [City] [varchar](50) NULL, [State] [varchar] (2) NULL, [P...

Fetch fields from a table that has the same relation to another table

I'll try to explain my case as good as i can. I'm making a website where you can find topics by browsing their tags. Nothing strange there. I'm having tricky time with some of the queries though. They might be easy for you, my mind is pretty messed up from doing alot of work :P. I have the tables "topics" and "tags". They are joined usi...

SQL Outer Join Function

I've written this function before but I can't seem to remember it and it didn't get into version control. Now, more to do with sleep deprivation than anything else, I can't remember how to rebuild it. Here's the idea. I have two tables, "regPrice" and "custPrice", with shared key "itemID." They both have a "price" column and custPrice a...

Select random sampling from sqlserver quickly

I have a huge table of > 10 million rows. I need to efficiently grab a random sampling of 5000 from it. I have some constriants that reduces the total rows I am looking for to like 9 millon. I tried using order by NEWID(), but that query will take too long as it has to do a table scan of all rows. Is there a faster way to do this? ...

Finding duplicate records in SQL based on combination of fields

I have a project where a significant piece of the pie will be to identify where a record is duplicated in the database (Sql Server 2005). I know the obvious ways to find a duplicate record. However, in this case, we want to be fairly smart about the process. The table(s) will contain information about a prospective customer(lead). Th...

Using SQL in Java with package java.sql

Hello, during a lecture my professor gave examples of several actions involving databases and the java.sql package. These examples were supposed to be uploaded online in a pdf file, but for some reason the names of all functions and class names aren't displaying with my pdf reader. I would like to know the equilavents of the following P...

How to copy indexes from one table to another in SQLSERVER

I need to copy the indexes from one table to another. There are a LOT of indexes and I don't want to recreate them from scratch. Seems error prone anyways. I have copied the structure using SELECT * INTO [BackupTable] FROM [OriginalTable] But that doesn't copy indexes, constraints, triggers etc Does anyone know how to do this? ...

Picking Fields in a Multiple-Field Index on MySQL

I have this MySQL query that I'd like to optimize: SELECT min(`topic_id`) FROM `gallery_topics` where `topic_id` > 11 AND `topic_poster` = 5 AND `topic_status` = 0 I wanted to add an index with multiple fields to optimize this query, so I added a key I named previous_by_author with these three keys in this order: topic_id topic_post...

Formatting an SQL numeric query result with an arbitrary number of decimal places

I have a database table with these two columns: Amount: numeric (18,0) DecimalPlaces: numeric (18,0) This table can store amounts in various currencies, with the decimal place removed from the amount (I can't change this data model). For example, there might be two rows like this: 1290, 2 (This is £12.90, needs to appear as "12.90"...

Problem Query

I am facing issues trying to write a query. My tables are laid out as follows: tblTicketIssues TicketID | RequesterID tblPersonnelProfile PersonnelID | FirstName | LastName tblTicketAttribute TicketID | Attribute | AttributeValue I have to display the following fields: TicketID, RequesterFullName, UrgentPriorityID, MediumPriori...

java.sql.SQLException: No suitable driver on Mac OS X Attempting to use Derby

Hello, I am getting a java.sql.SQLException: No suitable driver when I am trying to connect to a database with Java. I am on Mac OS 10.5 using the NetBeans IDE. It seems to be having trouble with the EmbeddedDriver, but I'm not sure what I am missing: public class A { Connection conn = null; public A(String URL...

How do you add Foreign Key Relationships?

I'm working with an existing SQL 2005 database that was not implemented with FK relationships between tables. I tried to add the relationships with a database diagram and my application immediately blew up trying to edit or insert any data that is tied to the new FK. dbo.person [person_id | firstname | lastname | dateofbirth] dbo.campa...

SQL - calculate percentage increase in values of a numeric column

Hi I want to calculate some trends based on the values I get in my SQL database. Particularly I am interested in getting the % increase of the values in one column (the value should be negative in case the values have decreased) Can someone please advice how I should do this? I cannot use stored procedures. Thanks. My table has the...

How to SELECT * INTO [temp table] FROM [Stored Procedure]

How do I do a SELECT * INTO [temp table] FROM [Stored Procedure]? Not FROM [Table] and without defining [temp table] ? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine Trying the same, but using a stored procedure that returns data, is not quite the same. select * into tmpBusLi...

UPDATE with SUM() in MySQL

My table: ID NAME COST PAR P_val S_val 1 X 5 0 1 0 1 y 5 0 2 0 1 z 5 0 0 5 2 XY 4 0 4 4 I need to update the PAR field with the SUM(S_val), grouped by ID: For ID 1 PAR should be SUM(SVAL) WHERE ID=1 For ID 2 PAR should be SUM(SVAL) WHERE ID=2 Expected ouput: ...

Separated string created in loop

I'm searching the best way to create a string separated with another in a loop. I mean, for example, SQL reader: StringBuilder sb = new StringBuilder(); while(reader.Read()) { sb.Append(reader[0]); sb.Append("<br />"); } string result = sb.ToString(); result = result.Remove(result.LastIndexOf("<br />")); // <- or creating SQL quer...

How do I translate this GROUP BY / MIN SQL query into LINQ?

I plan on using this in a subquery but can't figure out the correct syntax to translate the following query into LINQ: select ChapterID, min(MeetingDate) from ChapterMeeting group by ChapterID ...

Split SQL statements

Hello, I am writing a backend application which needs to be able to send multiple SQL commands to a MySQL server. MySQL >= 5.x support multiple statements, but unfortunately we are interfacing with MySQL 4.x. I am trying to find a way (hint: regex) to split SQL statements by their semicolon, but it should ignore semicolons in single...