SQL Server :: How To Union Two Queries Without Duplicates
Aug 27, 2010
I have a sql query that returns 4 columns CustName CustId CustZip CustPhone
I have a second sql query that returns the following 5 columns
CustName CustId CustZip CustEmail CustAddress
Both queries, query different data tables in the database, but return columns that are common to
both.
How do I union the two queries(Assuming a union is needed)
Which will result in no duplicates and an end result being the following output:
CustName CustId CustZip CustPhone CustEmail CustAddress
As you can see we want to not have duplicate values on output. So something like the following
is not acceptable:
Jeff Stamper 2222234 81224 498-300-2222
Jeff Stamper 2222234 81224 498-300-2222 js@jj.com 122 Mars Blvd
Karen Bops 3322234 81666 498-300-2222
Karen Bops 3322234 81666 498-300-2222 kb@lpo.com 322 Jamer Road
View 4 Replies
Similar Messages:
Jan 7, 2010
I have a linq to sql query where I have to perform union two set of records.
And I do not have equal number of fields, so added the null
eg my psuedo code is
[code]....
after googling some hours I came to understand that it is bug in 3.5 framework.
Now I want to retrieve the result.
How do I do thatI tried framing into two seperate iqueryable
var a= first query
var b =second query
ilist result =a.union b
This too results in the same error.
View 3 Replies
Jul 19, 2010
I have two gridviews, say 'A' and 'B'...I want to add all the rows of 'A' to 'B' while avoiding the duplicates....For eg:
View 10 Replies
Aug 24, 2010
How can I order this by count? so if it appears above and below the union statement, I want it on top
[Code]....
View 3 Replies
Mar 25, 2011
Below is my SQL query in which I have used UNION All In my outer query.so I want to select data onwards particulardate. [code]...
View 1 Replies
Sep 30, 2010
Is there a way to row two and three of the result set be returned so that the
following query:
[Code]....
View 10 Replies
Aug 4, 2010
I am a .net developer.I am not that strong in SQL.I need a simple query.This is from Northwind Database.
select 'Select All' as CustomerID,'Select All' as City union(select CustomerID, City from Customers
order by City)
i need to get output like 1st row should be Select ALl Select All and next rows should be output of this stat..(select CustomerID, City from Customers order by City)
i am not getting this...i can do by keeping the second query result in temp table and then using union... but i am looking without temp tables or simple query.
View 2 Replies
Oct 8, 2010
I am importing images names from a file into a database by using the code below. When I import the image names, I aslo want to add a date of "June 12, 2009" in the DATE column. Finally, I do not want to import any duplicates. Is there something I can change in this code to make that happen?
declare @cmd varchar(1000)
set @cmd = 'dir "D:imagesREVIVE" /b'
if (object_id('ImageTable..tblimages') is not null)
begin
insert into tblimages (imgname)
execute xp_cmdshell @cmd
delete e
from tblimages e
where ISNULL(e.imgname, '') = ''
select *
from tblimages
end
else
create table tblimages ([id] int identity(1,1), imgname varchar(1000))
insert into tblimages (imgname)
execute xp_cmdshell @cmd
delete e
from tblimages e
where ISNULL(e.imgname, '') = ''
select *
from tblimages
View 7 Replies
Nov 29, 2010
I basically have a series of tables from one database that have an identical structure thus making retrieval of all records fairly easy (I just use a UNION ALL statement). However I need a list of values from another table within a different database that contains information regarding the group of the data.
I so far have the following:
SELECT Table1.* FROM Table1 UNION ALL SELECT Table2.* FROM Table2 UNION ALL SELECT Table3.* FROM Table3 UNION SELECT Database.dbo.SRFILE.SR_GROUP FROM Database.dbo.SRFILE INNER JOIN Database.dbo.SRFILE.SRONUMBER = Table1.Incidentx
I keep receiving a unable to parse message however all I need to retreive is the SR_GROUP value but just don't know the correct syntax. Is it actually possible to do this as the structure of SRFILE is not the same as Table1/2/3.
View 3 Replies
Feb 25, 2011
I am writing code to insert values from an xml file. I check for a duplicate before inserting and delete if there is a match. Is there a more effecient way to prevent duplicates before insert a record? my code: private void LoadFData() { SqlConnection oConn = new SqlConnection(ConnectionString); SqlCommand oCommand = new SqlCommand(); oCommand.Connection = oConn; oCommand.CommandType = CommandType.Text; oConn.Open(); // Load committee master files string[] strFiles = System.IO.Directory.GetFiles("fff", "ffff.dta", System.IO.SearchOption.AllDirectories); for (int i=0; i
View 2 Replies
Feb 16, 2011
my statment is: SELECT TOP (20) PERCENT GroupName from [Group] ORDER BY NewID() union SELECT TOP (10) PERCENT GroupName from [Group] i get this error
View 1 Replies
Sep 14, 2010
I have to subqueries and i need them to be sidebyside but when i "union all" one goes under.
What is the way i can have them appear beside each other?
View 5 Replies
Sep 11, 2010
On satisfying the If condition, I need to run 2 MS SQL update queries. How can I accomplish this? I am using C# as the server side language.
View 6 Replies
Dec 21, 2010
I want to find the sum of each tables fileld. how can do this. i tried the following.but could nt get the correct sum. "SELECT SUM(gb) FROM data20 union all SELECT SUM(gb) FROM data10 union all SELECT SUM(gb) FROM data5 union all SELECT SUM(gb) FROM data6"
View 7 Replies
Sep 6, 2010
I want to combine two queries like :
select name as name1 from table1 where id = 6
and
select name as name2 from table1 where id = 2.
I need a single resultset. How can I do that?
View 26 Replies
Mar 24, 2011
How to debug queries and stored procedures in sql server 2008 like we doing in vs
View 1 Replies
Feb 23, 2011
I have a menu table which has a (mostly) unlimited number of levels/tiers:
[Code]....
I also have a working function which will give the up and down tiers based on a given MainMenuId:
[Code]....
My question is, is there a better way to do this such as combining the two querries? Every attempt I have made results in a "The Statement terminated. The maximum recursion 100 has been exhausted before statement completion"
View 4 Replies
Jan 29, 2011
how to run multiple queries on a single button click. Actually I want whatever a user write in text box and click the button the text would get inserted in table category and also a table name project should get updated. I am using Sql server 2005 and c#
View 2 Replies
Jan 11, 2011
In my stored procedure there are 8 queries for 8 tables.Each query has joins and sub queries. I am passing parameters & stored procedure name from front end(designed in asp.net 3.5) can we execute that 8 queries at the same time i.e. parallel execution so that I can minimize stored procedure execution time?
View 6 Replies
Nov 15, 2010
Alright, we have two database servers, one is owned by us, one owned by a partner. Our partners have been having issues lately that have been causing us numerous sql timeout errors, which takes down our whole system. We'd prefer to limit the time those queries can take to say 20 seconds max, otherwise quit trying and throw the error (which we can catch with a try/catch block). Couple of questions: Is there a way to set a particular db/connection string to timeout after X seconds? Is this the best way to quarantine that other system? or are there better ways to go about this?
View 2 Replies
Feb 19, 2010
I'm completely new to both VB.NET and JSON, and I'm trying to figure out how to do SQL queries towards an SQL 2005 Express server and format the return value to JSON. I got the queries working using this (perhaps very newbie-like) code;
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI
Imports System.Web.UI.WebControls
Partial Public Class SQLConnect
Inherits System.Web.UI.Page
'Defines SQL variables
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
Dim ReadData As String
Dim i As Integer
Sub Click(ByVal s As Object, ByVal e As EventArgs)
'Define SQL address, database, username and password
con.ConnectionString = "Data Source=localhostSQLEXPRESS;Initial Catalog=tempdb;User ID=tesst;Password=test"
Try
'Initialize connection
con.Open()
'Specify SQL query
cmd = New SqlCommand("SELECT * FROM Member", con)
'Execute query, dump result array into variable
dr = cmd.ExecuteReader()
messageLabel.Text = "<br />Connection established<br />"
While (dr.Read)
i = 0
For i = 0 To dr.FieldCount - 1
'Dump query results into a variable
ReadData += dr.Item(i).ToString
Next
End While
'Print query results
messageLabel2.Text = ReadData
'Close connection
con.Close()
Catch ex As Exception
messageLabel.Text = "<br />Connection failed<br />"
End Try
End Sub
End Class
I have been looking at this, and I would love to see some code examples using this class or any other good method.
View 3 Replies
Aug 11, 2010
We moved to a new SQL server and I started getting an error on one of my sps only when I ran it from a website. If I manually ran the sp it worked fine. The error I got was this System.Data.SqlClient.SqlException: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. I can tell you that this sp does some things, then calls another sp which writes some sql to open an excel file and insert the data. Here is the pseudo
SET @sql = 'INSERT INTO dbo.TempParts (partKey, partKeyNumeric, partKeyAlpha, PartNumber, [Description], Comment, notAvailable ,BatchID)
View 6 Replies
Apr 1, 2011
We have an ASP.NET / Silveright web application. The silverlight client displays user specific data in a graphical form - it requests the data from the server: Problem: Getting this data is expensive, due to the underlying database queries that the server has to perform - so the client has to wait... Optimisation Idea: We run the database queries at regular intervals on the server, writing the results to a 'userdata' table in a database 'close' to where the ASP.NET server runs. The process of running the queries and writing the data to the tables is performed by a 'data collection' service, which is separated from the ASP.NET server.
When the client requests data the server retrieves it from a 'userdata' table. This should be nice and quick - we probably have the 'userdata' tables on the same machine as the ASP.NET server. We also have the added benefit that the client sees data even if the underlying database is offline. Of course the data is not live - but all data is potentially old as soon as it reaches the client. So now my Problem: The 'data collection' service needs the user credentials in order to perform these database queries (because each user gets different results for the same query).
How can I store user credentials in a database, in an acceptable 'secure' way? Such that the 'data collection' can impersonate a user to perform the database queries. Our initial scenario is based upon using windows integrated login to the database.
View 1 Replies
Aug 31, 2010
I need to use Union for below Linq queries. I could use Union it if it was simple "select new whith{" but Now that I Specified Class, I encounter Error using union
[Code]....
View 3 Replies
Feb 18, 2010
1) we have only one select query to select 5 column form the table(i.e. select col1,col2,col3,col4,col5). so in that case should we go for stored procedure Or we can directly write the select query in the .Cs file only ( means in Database layer). when we should go for the SQL server stored procedure and when we should go for in line query in Visual Studio itself.
2)how to decide when to use Clustered and when to use Non Clustered index?
View 4 Replies
