July 2, 2008 at 11:50 pm
Hi All,
I am facing some problem with a SQL join. I have three table with the following structure
Table : Agent(AgentID,AgentName)
AgentID AgentName
1 James
2 Mary
3 Ronny
Table : Project(ProjectID,ProjectName)
ProjectID ProjectName
1 Genuine
2 Real
3 XLN Telecom
Table : AgentSession(AgentID,Date,PrimaryProjectID,SecondaryProjectID)
AgentID Date PrimaryProjectID SecondaryProjectID
1 02 july 2008 1 0
1 03 july 2008 1 2
1 04 july 2008 2 0
2 02 july 2008 2 1
3 02 july 2008 2 0
2 04 july 2008 2 3
3 02 july 2008 1 0
3 02 july 2008 3 1
Now what I want is all data from AgentSession table just AgentName instead of AgentD and ProjectName instead of
Primary and Secondary Project ID like the following
Agent Date PrimaryProject SecondaryProject
James 02 july 2008 Genuine NULL
James 03 july 2008 Genuine Real
James 04 july 2008 Real NULL
Mary 02 july 2008 Real Genuine
Ronny 02 july 2008 Real NULL
Mary 04 july 2008 Real XLN Telecom
Ronny 02 july 2008 Genuine NULL
Ronny 02 july 2008 XLN Telecom Genuine
Please help me to solve this problem.
Thanks in advance.
Warm and Personal Regards,
Dinendra
July 3, 2008 at 12:04 am
DECLARE @Agent TABLE (AgentID int,AgentName varchar(100))
INSERT INTO @Agent
SELECT 1,'James' UNION SELECT 2,'Mary' UNION SELECT 3,'Ronny'
DECLARE @Project TABLE(ProjectID int,ProjectName varchar(100))
INSERT INTO @Project
SELECT 1,'Genuine' UNION SELECT 2,'Real' UNION SELECT 3,'XLN Telecom'
DECLARE @AgentSession TABLE(AgentID int,Date datetime,PrimaryProjectID int,SecondaryProjectID int)
INSERT INTO @AgentSession
SELECT 1, '02 july 2008', 1,0 UNION
SELECT 1, '03 july 2008', 1,2 UNION
SELECT 1, '04 july 2008', 2,0 UNION
SELECT 2, '02 july 2008', 2,1 UNION
SELECT 3, '02 july 2008', 2,0 UNION
SELECT 2, '04 july 2008', 2,3 UNION
SELECT 3, '02 july 2008', 1,0 UNION
SELECT 3, '02 july 2008', 3,1
SELECT B.AgentName,Convert(varchar(20),A.Date,106),C1.ProjectName PrimaryProject,
C2.ProjectName SecondaryProject FROM @AgentSession A
LEFT OUTER JOIN @Agent BONB.AgentID=A.AgentID
LEFT OUTER JOIN @Project C1ONC1.ProjectID=A.PrimaryProjectID
LEFT OUTER JOIN @Project C2ONC2.ProjectID=A.SecondaryProjectID
July 3, 2008 at 12:07 am
Alternatively you can use subqueries like:
SELECT
(Select AgentName From @Agent Where AgentID=A.AgentID) AgentName,
Convert(varchar(20),A.Date,106),
(Select ProjectName From @Project Where ProjectID=A.PrimaryProjectID) PrimaryProject,
(Select ProjectName From @Project Where ProjectID=A.SecondaryProjectID) SecondaryProject
FROM @AgentSession A
July 3, 2008 at 1:14 am
Thank you.I really appreciate your help.
July 4, 2008 at 3:44 am
Don't know why you guys are making it so hard. What is wrong with just:
SELECT
Agt.AgentName,
Ags.Date,
PrimProj.ProjectName As PrimaryProject,
SecProj.ProjectName AS SecondaryProject
FROM
AgentSession AS Ags
JOIN Agent AS Agt on Agt.AgentID = Ags.AgentID
JOIN Project As PrimProj on PrimProj.ProjectID = Ags.PrimaryProjectID
LEFT JOIN Project As SecProj on SecProj.ProjectID = Ags.SecondaryProjectID
The last join is a left join since not every agent session has a secondary project...
July 4, 2008 at 7:39 am
Ben,
I don't think we are making it too hard 🙂 - your solution is almost identical to this of Hari Sharma. The only difference is use of INNER or OUTER joins, as far as I can see. The rest of Hari's post is just creating the environment, so that everyone can repeat it - only the last select is actually the solution.
(However, I agree that the other variant, with correlated subqueries... also by Hari Sharma..., is too complicated, probably poorly performing and I wouldn't recommend it).
July 4, 2008 at 9:27 am
Vladan,
Indeed. I didn't bother to look twice 😉 but just noticed a whole bunch of SQL while the solution was fairly simple.
And I second your sub-select comment.
Regards,
Ben
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply