sql

Removing duplicate records.

Hi, I am using the following query select SS.sightseeingId, SS.SightseeingName, SS.displayPrice, SST.fromDate from tblSightseeings SS inner join tblSightseeingTours SST on SS.sightseeingId = SST.sightseeingId where SS.isActive = 1 and SS.isDisplayOnMainPage = 1 and getting result like this 2 Dinner Cruise Bat...

Convert inner join to SQL Subquery

Dear Friend, my SQL Query is as follow: i m using inner join in that query... i want to modify that into subquery select distinct Auditdata.ID, ns.ProviderMaster_ID as CDRComment from Auditdata AuditData inner join AuditMaster am on am.ID = AuditData.AuditMaster_ID inner join HomeCircleMaster hcm on hcm.Ori_CircleMaster...

SQL injection attack on asp registration form pages?

I need to know the process of the SQL injection attack on registration form made by asp or asp.net ? this is a crucial question to me. thank you ...

SQL How to Limit BOTTOM x rows (PostgreSQL)

I have a query like: SELECT EXTRACT(WEEK FROM j.updated_at) as "week", count(j.id) FROM jobs WHERE EXTRACT(YEAR FROM j.updated_at)=2009 GROUP BY EXTRACT(WEEK FROM j.updated_at) ORDER BY week Which works fine, but I only want to show the last say 12 weeks, LIMIT 12 works, but only gives me the first twelve and I need th...

programmatically replace SELECT fields in sql query to determine number of results

Given any query (grouping, joins, and subqueries are all possible in any combination), I'd like to break it up with pagination as efficiently as possible. Right now I just run the query, then count the number of rows returned to PHP, figure out how many pages of data that is, display the ones that go on the current page, and output the p...

Delete data from multiple table

I have the below query which will delete data from multiple tables its not working please help me : DoCmd.RunSQL ("delete tblTesting,tblCodes,tblContract,tblScheduled,tblAbsense,tblClock from tblTesting,tblCodes,tblContract,tblScheduled,tblAbsense,tblClock where tblTesting.TestId = " & lstResults.Column(1) & " And tblTesting.Empid = ...

What is the effect of omitting size in nvarchar declaration ..

Hi, I usually defining size when declaring parameters in my SP, like : @myParam nvarchar(size) or when I casting or converting: CAST(@myParam AS nvarchar(size)) Recently I removed size from my CAST functions like: CAST(@myParam AS nvarchar) and I'm bit worried if that is going to come and bite me when least expected :-(, since I...

SQL select joined string for set of ids

I have to deal with a mssql database and the information given in a table like this: Users: ID Name Countries -------------------- 1 User1 1,2,3 2 User2 2,5 Countries: ID Country ---------- 1 Australia 2 Germany 3 USA 4 Norway 5 Canada Now, what i am looking for is a select statement that will give me a result like thi...

How to determine when a time stamp does not exist in a table

I have a table that receives data on an hourly basis. Part of this import process writes the timestamp of the import to the table. My question is, how can I build a query to produce a result set of the periods of time when the import did not write to the table? My first thought is to have a table of static int and just do an outer join ...

SQL Server 2008 Rows to 1 CSV field

We're on SQL Server 2008 and I'm trying to figure out if there's a way to have a stored procedure return my results in 1 CSV field for example: SELECT TOP 4 carModels FROM dbo.Models would return Jeep Honda Mitsubishi Ford I would like this returned in 1 field like so: Jeep,Honda,Mitsubishi,Ford I know we can do this with an asse...

Why does the count function in SQL seem to be doing more than counting the column I ask it to?

I have an INSERT query that is pulling data from two tables and inserting that data into a third table. Everything appears to be working fine except that the COUNT part of the query isn't returning the results I would expect. The first set of tables this query runs is MIUsInGrid1000 (number of rows = 1) and Results1000 (number of rows ...

Efficient way to Query a Delimited Varchar Field in SQL

I have an SQL Server 2005 table that has a varchar(250) field which contains keywords that I will use for searching purposes. I can't change the design. The data looks like this... Personal, Property, Cost, Endorsement What is the most efficient way to run search queries against these keywords? The only thing I can think of is t...

Is possible to get a constant value declared in a database package? (SQL or Java)

I have some Packages in a Oracle database. They contain stored procedures, functions and constants. I can call functions, in Java, using a Java CallableStatement. Also, I could execute a SQL statement as "select package1.function1(value1) from dual;". But I can't find how to get the value of a constant declared in the package, in Java. ...

Copy Trigger slows database operations down considerably

Hello Our original database has called first, middle, last for names of people. We have now created fields called firstname, middlename, and lastname. While we are going through this transition I have created a trigger to copy data from first to firstname, middle to middlename, and last to lastname anytime one the previous fields receiv...

PHP / SQL picture upload to a database.

I need a php code and sql code that will let someone upload an image to a database. The only thing I can find is very glictchy and not accepted by some browsers. Any ideas? ...

Need help with a SQL query that combines adjacent rows into a single row

Hi all, I need a solution to a problem that has the table structure as listed below. Input 1 1/1/2009 Product1 2 2/2/2009 Product2 3 3/3/2009 Product3 4 4/4/2009 Product4 5 5/5/2009 Product5 Output 1 1/1/2009 2/2009 Product1 2 3/3/2009 4/4/2009 Product3 3 5/5/2009            Product5 I tried using CTE. But was not ...

implementing "update if exists" in Doctrine ORM

I am trying to INSERT OR UPDATE IF EXISTS in one transaction. in mysql, I would generally use DUPLICATE KEY ("UPDATE ON DUPLICATE KEY".) I'm aware of many solutions to this problem using various sql variants and subqueries, but I'm trying to implement this in Doctrine (php ORM). It seems there would be Doctrine methods for doing this...

Getting information (on click) that was used to programatically generate asp controls

How may one get information that was used to programatically generate asp controls? For example, I pulled a DataTable of user objects from the database and have organized them on a page, listing groupings such as a list of employees directly under the employer for each employer. On the page, I list each user's Username as a LinkButton....

Dealing with intermittent Database Connectivity by Writing/Executing SQL Script From Scala/Java

I am developing an application that needs to store information to a database. I would like to use a Scala solution if possible. If the database connectivity fails for some reason, I would like to write the raw SQL statements that would have been executed to a .sql script file. The idea is that when/if the connectivity to the database is ...

MySQL Range and Average

I'm wondering if in MySQL you are able to find a range within values along with the average in a query. Assume the table below please: ----------------------------------------- | ID | VALUE | ----------------------------------------- | 1 | 30 | -----------------------------------...