.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
ASPEvil
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Post New Web Links

How to union two queries without duplicates

Posted By:      Posted Date: August 27, 2010    Points: 0   Category :ASP.Net
 


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 Complete Post


More Related Resource Links

How to union two MDX queries

  

Hi,

Iam doing reports using MDX queries,i need to do union of these two MDX queries,"[PA to ID SUM]","[PD to AI SUM]" these are statuses and also parameters for  report,actually i thought to display data in seperate table for each of this MDX query,but if i select "PA to ID SUM" , the table used to display"PD to AI SUM" shows error.I tried to hide table in the report ,but the status "PA to ID SUM","PD to AI SUM" like that there are 40 statuses,for 40 statuses i need to diplay 40 tables.i think hiding the table is not a good idea ,if there is any possibility to do union of these two mdx queries,i can display all data in one table

Actually the requirement is , 40 status are there for each orderid,status is nothing but no of minutes taken by the orderid to complete that status, i need to get min ,max ,avg,median ,percentile for  each status

WITH
MEMBER Measures.[PAtoID_MIN] as MIN([DimOrder].[Order Id].[Order Id],[Measures].[PA to ID SUM])
MEMBER Measures.[PAtoID_MAX] as MAX([DimOrder].[Order Id].[Order Id],[Measures].[PA to ID SUM])
MEMBER Measures.[PA75] as MIN(TOPPERCENT([DimOrder].[Order Id].[Order Id],75,[Measures].[PA to ID SUM]),[Measures].[PA to ID SUM])
MEMBER Measures.[PA80] as MIN(TOPPERCENT([DimOrder].[Order Id].[Order Id],80,[Measures].[PA to ID SUM]

Can i use UNION ALL between two queries using IF ELSE?

  

HI All,

I have two queries with IF ELSE in them but dont know if i can use union all between them. i want to get the results from both the queries in one window one below another.

IF (SELECT EtlLoadId
 FROM  ssislogs.audit.processcontrol
 WHERE SubjectArea = 'UaqaFacetsImp') > 0
SELECT pc.SrcFileName + ' - '+  CONVERT(VARCHAR(10), pc.[RecordCount] ) AS FacetsImpCount
 FROM  ssislogs.audit.processcontrol pc left outer join
 Uaqa.LettersAndCardsExports lc
on pc.etlloadid = lc.etlloadid
  WHERE pc.subjectarea = 'UaqaFacetsImp'
ELSE
SELECT 'No input file' AS FacetsImpCount

IF (SELECT EtlLoadId
 FROM  ssislogs.audit.processcontrol
 WHERE SubjectArea = 'UaqaFazalImp') > 0
SELECT pc.SrcFileName + ' - '+  CONVERT(VARCHAR(10), pc.[RecordCount] ) AS FazalImpCount
 FROM  ssislogs.audit.processcontrol pc left outer join
 Uaqa.LettersAndCardsExports lc
on pc.etlloadid = lc.etlloadid
  WHERE pc.subjectarea = 'UaqaFazalImp'
ELSE
SELECT 'No input file' AS FazalImpCount

Thanks


Need Syntax To Make Results of LINQ Union Return Non-Generic Type

  

Hi.

I have the below SQL which works just fine:

SELECT     Message, CreateDate, AccountId, AlertTypeId
FROM       dbo.Alerts
UNION
SELECT     TOP (100) PERCENT Status, CreateDate, AccountId,
                          (SELECT     10 AS Expr1) AS AlertTypeId
FROM         dbo.StatusUpdates
WHERE AccountId = PassedInParameter
ORDER BY CreateDate DESC

I have been going about trying to convert the above into a LINQ method and am having problems with return types.
As the above SQL illustrates, I am querying two distinct LINQ tables - Alerts and StatusUpdates -
and then attempting to perform a Union on them. In order to do the union in LINQ, I have to have agreeing types. So, I
am using "var" to make the results of "alerts" and "updates" generic before the union.  

public IList GetStatusUpdatesAsAlertsByAccountId<T>(Int32 accountId)
        {
            using (WorkbookDataContext dc = _conn.GetCont

Which tier do Linq queries belong?

  

Started in middle of a new project.  Our design architecture uses an n-tier (not MVC - no controllers) structure with Presentation, Data, Business and Service tiers.  However the lead analyst has the majoriety of code all in the Business tier claiming it all contains Business logic.  Our data tier is nothing more than a single .dbml model of the primary database to support all of the Linq queries in the Business tier.  To my mind Linq queries and classes they use are data access and belong in the data tier  but our lead analyst says they contain business logic therefore have to be in business layer.  


Combining two LDAP queries

  

Hey guys, I have these two queries I'd like to combine into one.  Is there any way of doing it?
Query 1: (&(!cn=SystemMailbox{*})(&(&(ou>="")(name=executive*)(objectCategory=organizationalUnit)(ou=*operations))))
Query 2: (&(!cn=SystemMailbox{*})(&(ou>="")(name=50*)))
Thanks in advance.

Hey guys, I have these two queries I'd like to combine into one.  Is there any way of doing it?


<

Data Points: LINQ Projection Queries and Alternatives in WCF Services

  

LINQ's ability to project randomly shaped data into anonymous types can be a blessing and a source of frustration, says Julie Lerman. It's great when you just need to grab a special view of your data, but it can be problematic in certain circumstances. She explains what the limitations are and how to get around them.

Julie Lerman

MSDN Magazine May 2010


Data Points: Precompiling LINQ Queries

  

Did you know that by precompiling LINQ queries you might actually be degrading your app's performance if you're not careful? Julie Lerman explains how to ensure you're not re-precompiling queries each time and losing the expected performance benefits across post-backs, short-lived service operations and other code where critical instances are going out of scope.

Julie Lerman

MSDN Magazine March 2010


Toolbox: Easy LINQ Queries, Becoming A Better Developer, And Logging Help

  

This month Toolbox provides help with error logging, finds an app that helps you write LINQ queries, and explores the non-technical side of development.

Scott Mitchell

MSDN Magazine September 2008


Parallel LINQ: Running Queries On Multi-Core Processors

  

Use Parallel Language Integrated Query (PLINQ) to take advantage of multi-processor computers and the newer wave of multi-core processors.

Joe Duffy and Ed Essey

MSDN Magazine October 2007


How to stop the repeated database queries for roles

  

Hello, friends,

We have a web application using VS 2008, c#. We try to filter siteMap nodes based on security roles. We have our customized the mether GetRolesForUser() in RoleProvider class to determine a user's role. In this method, roles will be returned by querying an SQL Server database.

However, we found that each time a page was loaded/refershed, this  GetRolesForUser() was called, and the database would be queried. This is too MUCH and expensive.

We thought the roles should be queried only once when a user logs in. After that, role info should be stored somewhere for this user, rather than query DB all the time.

Any ideas, reference paper, snipet,...,?

Thanks a lot!


order by in combination with union

  

 Hi All,

How can I order this by count? so if it appears above and below the union statement, I want it on top

	SELECT distinct BIER.Naam, BIER_L2.L2_Naam, BIER_L3.L3_Naam from BIER_MATCH, BIER, BIER_L2, BIER_L3
	where BIER_L2.L2_ID = BIER.L2_ID and BIER_L2.L3_ID = BIER_L3.L3_ID and BIER.ID = BIER_MATCH.BIER_ID 
	and RECEPT_ID = @RECEPT_ID 

	
	UNION  
	
	--L2
	SELECT distinct BIER.Naam, BIER_L2.L2_Naam, BIER_L3.L3_Naam from BIER_MATCH, BIER, BIER_L2, BIER_L3
	where BIER_L2.L2_ID = BIER.L2_ID and BIER_L2.L3_ID = BIER_L3.L3_ID and RECEPT_ID = @RECEPT_ID 
	and BIER_MATCH.BIER_ID is null


 


Time Difference in Linq queries

  

Hi All,

 

I have my Linq query in which i need to find time difference(not days difference).

The field in database is datetime.

Dim ldtToday As DateTime = TimeOfDay

Dim SR_RM_InnerJoin = From SR In lcntxRT60Entities.RtSReports _

Join RM In lcntxRT60Entities.RtRMaster On SR.RtRMaster.ReportID Equals RM.ReportID _

Select SR, RM _

Display 'Top Queries Over Previous 30 Days' to users

  

In the Site Usage Reports, there is a section for 'Top Queries Over Previous 30 Days'. I would like to display this information to users. 

Is there a existing webpart for this? 
Is the data stored in a list that can be used for this purpose?

Thanks,

Steve Clark
Twin-Soft.com


How to create strongly typed datasets with access parameter queries

  

Hi

How can you create strongly typed datasets using an access database against access select statements that use parameters?

The problem is VS.Net doesn't allow select queries with parameters to be dragged onto a form, it only allows access queries without parameters!

I also tried the dataadapter wizard, but again it only allows me to select queries without parameters?

Many thanks in advance

 


IEnumerable.Except incorrectly removes duplicates

  
IEnumerable.Except is expected to receive a second IEnumerable with the elements to be removed from the first. But when the first collection contains duplicates, those are removed too, despite not being present on the second. Check in the following example that 2.3 appears twice on numbers1 but only once on the output. double[] numbers1 = { 2.0, 2.1, 2.2, 2.3, 2.3, 2.4, 2.5 }; double[] numbers2 = { 2.2 }; IEnumerable<double> onlyInFirstSet = numbers1.Except(numbers2); foreach (double number in onlyInFirstSet) Console.WriteLine(number); /* This code produces the following output: 2 2.1 2.3 2.4 2.5 */
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend