Click here to Skip to main content
14,607,232 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,

I have 2 Queries

select Users.UserId,Users.EmpNo,EmpPayments.SalBasic from Users join EmpPayments on Users.UserId=EmpPayments.UserId where EmpPayments.MonthId=6 and EmpPayments.YearId=2010 and Users.MainContractorSw='Y' and Users.UserId=43

select Users.UserId,Users.EmpNo,EmpPayments.SalBasic from Users join EmpPayments on Users.UserId=EmpPayments.UserId where EmpPayments.MonthId=6 and EmpPayments.YearId=2010 and Users.MainContractorId=43



Now I want to use these two Queries as one query and the result should be Combined of the Individual Queries
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 1

Hi Vignesh,

Please use UNION to combine both result like:

select Users.UserId,Users.EmpNo,EmpPayments.SalBasic from Users join EmpPayments on Users.UserId=EmpPayments.UserId where EmpPayments.MonthId=6 and EmpPayments.YearId=2010 and Users.MainContractorSw='Y' and Users.UserId=43
UNION
select Users.UserId,Users.EmpNo,EmpPayments.SalBasic from Users join EmpPayments on Users.UserId=EmpPayments.UserId where EmpPayments.MonthId=6 and EmpPayments.YearId=2010 and Users.MainContractorId=43



Please do let me know, if you have any doubt.

Please provide "Vote":thumbsdown: if this would be helpful, and make "Accept Answer" if this would be correct answer.:rose:

Thanks,
Imdadhusen
   
Comments
Bassam Abdul-Baki 18-Nov-10 9:23am
   
I'd suggest changing your thumbs down icon to the thumbs up one for helpful replies.

Please provide "Vote" Thumbs Down if this would be helpful....
thatraja 18-Nov-10 10:37am
   
Good catch Bassam :-)
Rate this:
Please Sign up or sign in to vote.

Solution 2

So instead of "Users.MainContractorSw='Y' and Users.UserId=43" and "Users.MainContractorId=43" you want "(Users.MainContractorSw='Y' and Users.UserId=43) or (Users.MainContractorId=43)".
   
Comments
Sunasara Imdadhusen 18-Nov-10 9:17am
   
Good one
Sunasara Imdadhusen 18-Nov-10 9:17am
   
This also can give same result
Sunasara Imdadhusen 18-Nov-10 9:25am
   
Ok. can you tell me what it will return what should be?
Vigneshb6 18-Nov-10 9:27am
   
First Query Results Main Contractor Results and his salary
and the second query should return the SubContractors Results and there salaries of that particular Main Contractor. If i use the Union i will get the results. but if there are no MainContractors details in the EmpPayments table it should not display the SubContractor's Details actually i have to use this query in Crystal Report.
Vigneshb6 18-Nov-10 9:31am
   
Sorry i can't use intersect here
Vigneshb6 18-Nov-10 23:29pm
   
Hi Sunasara I solved my problem by using subqueries and Union thanx for the response.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100