February 10, 2012 at 6:21 am
hi
May you please assist in linking the below queries or suggest best way to handle linking between the two. I am avoiding to create two views for each and then link. Thought there might be a better way for do it straight on one code.
( common fields btn queries to link on: User_Id = Opp_View.Owner_Id)
SELECT User_Id,User_FirstName + ' '+ User_Name AS Name
FROM dbo.ADMN_User_Details
where User_Title = 'Key Account Executive' as Main
left outer join ( ON User_Id = Opp_View.Owner_Id)
SELECT Opp_View.Close_Date, Opp_View.Status, O_Request_New___Review.O_Request_New___Review,Opp_View.Owner_Id,
A_Super_Regions.A_Super_Regions, A_Total_no_of_Employees.A_Total_no_of_Employees,
A_Scheme_Code.A_Scheme_Code, Company.State_Province, ADMN_User_Details.User_FirstName,
ADMN_User_Details.User_Name, A_Scheme_Footprint.A_Scheme_Footprint
FROM ((((((AbsaWorkplaceBanking.dbo.A_Scheme_Footprint A_Scheme_Footprint
INNER JOIN AbsaWorkplaceBanking.dbo.Company Company ON A_Scheme_Footprint.Client_Id=Company.Client_Id)
INNER JOIN AbsaWorkplaceBanking.dbo.Opp_View Opp_View ON Company.Client_Id=Opp_View.Client_Id)
LEFT OUTER JOIN AbsaWorkplaceBanking.dbo.A_Total_no_of_Employees A_Total_no_of_Employees
ON Company.Client_Id=A_Total_no_of_Employees.Client_Id)
LEFT OUTER JOIN AbsaWorkplaceBanking.dbo.O_Request_New___Review O_Request_New___Review
ON Opp_View.Opp_Id=O_Request_New___Review.Client_Id) LEFT OUTER JOIN AbsaWorkplaceBanking.dbo.ADMN_User_Details ADMN_User_Details
ON Opp_View.Owner_Id=ADMN_User_Details.User_Id) LEFT OUTER JOIN AbsaWorkplaceBanking.dbo.A_Super_Regions A_Super_Regions
ON Opp_View.Client_Id=A_Super_Regions.Client_Id) LEFT OUTER JOIN AbsaWorkplaceBanking.dbo.A_Scheme_Code A_Scheme_Code
ON Opp_View.Client_Id=A_Scheme_Code.Client_Id
WHERE O_Request_New___Review.O_Request_New___Review='New' AND A_Scheme_Code.A_Scheme_Code<>''
AND Opp_View.Status = 3 and RIGHT(CONVERT(VARCHAR(10), Opp_View.Close_Date, 103), 4) >= '2011'
Thanks in advance
February 10, 2012 at 6:31 am
You can use "derived tables" for this kind of thing.
Would look something like this:
SELECT User_Id,User_FirstName + ' '+ User_Name AS Name
FROM dbo.ADMN_User_Details
left outer join (SELECT Opp_View.Close_Date, Opp_View.Status, O_Request_New___Review.O_Request_New___Review,Opp_View.Owner_Id,
A_Super_Regions.A_Super_Regions, A_Total_no_of_Employees.A_Total_no_of_Employees,
A_Scheme_Code.A_Scheme_Code, Company.State_Province, ADMN_User_Details.User_FirstName,
ADMN_User_Details.User_Name, A_Scheme_Footprint.A_Scheme_Footprint
FROM ((((((AbsaWorkplaceBanking.dbo.A_Scheme_Footprint A_Scheme_Footprint
INNER JOIN AbsaWorkplaceBanking.dbo.Company Company ON A_Scheme_Footprint.Client_Id=Company.Client_Id)
INNER JOIN AbsaWorkplaceBanking.dbo.Opp_View Opp_View ON Company.Client_Id=Opp_View.Client_Id)
LEFT OUTER JOIN AbsaWorkplaceBanking.dbo.A_Total_no_of_Employees A_Total_no_of_Employees
ON Company.Client_Id=A_Total_no_of_Employees.Client_Id)
LEFT OUTER JOIN AbsaWorkplaceBanking.dbo.O_Request_New___Review O_Request_New___Review
ON Opp_View.Opp_Id=O_Request_New___Review.Client_Id) LEFT OUTER JOIN AbsaWorkplaceBanking.dbo.ADMN_User_Details ADMN_User_Details
ON Opp_View.Owner_Id=ADMN_User_Details.User_Id) LEFT OUTER JOIN AbsaWorkplaceBanking.dbo.A_Super_Regions A_Super_Regions
ON Opp_View.Client_Id=A_Super_Regions.Client_Id) LEFT OUTER JOIN AbsaWorkplaceBanking.dbo.A_Scheme_Code A_Scheme_Code
ON Opp_View.Client_Id=A_Scheme_Code.Client_Id
WHERE O_Request_New___Review.O_Request_New___Review='New' AND A_Scheme_Code.A_Scheme_Code<>''
AND Opp_View.Status = 3 and RIGHT(CONVERT(VARCHAR(10), Opp_View.Close_Date, 103), 4) >= '2011' ) AS SecondQuery
ON Admin_User_Details.User_Id = SecondQuery.Owner_Id
where User_Title = 'Key Account Executive' as Main
You put the second query in parentheses in the From clause, give it a "table alias" using "AS", and then join to it just like any other table or view.
You can do the same thing with a Common Table Expression (MSDN has instructions on those). Same idea, slightly different syntax.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2012 at 6:43 am
thanks a lot.
However i want to be able to view fields from both derived table, i can only see t 3 fields selected from the first query and the one from the below query i cant view.
Is it possible to do that?
You help is much appreciated
February 13, 2012 at 6:26 am
You can add any column in the sub-query to the outer Select statement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply