April 17, 2009 at 1:22 pm
Hi Everyone,
I need a little help with a query. I have 2 tables
First Table tblSalesMan has the following columns:
"SalesmanId" and "SalesmanName"
Second table tblSales has the following columns:
"SalesNo" "SalemanId_1" "SalesmanId_2" "SalesmanId_3" "SalemanId_4"
The sales personnel split commission for sales. I need a query to match the salesman names to their Id.
Output Example
"SalesNo", "SalesmanId_1", "SalesmanName_1", "SalesmanId_2", "SalesmanName_2" etc.....
Thanks in advance,
~John
April 17, 2009 at 1:45 pm
Is there any chance you can redesign that second table? A vertical arrangement is going to be MUCH more efficient, and the horizontal one will cause problems later in the life of the database.
If not, then you'll probably want to use something like this:
SELECT
Sales.*
,SM1.SalesmanName AS [Salesman1]
,SM2.SalesmanName AS [Salesman2]
,SM3.SalesmanName AS [Salesman3]
,SM4.SalesmanName AS [Salesman4]
FROM
tblSales Sales
RIGHT OUTER JOIN tblSalesMan SM1
ON Sales.SalesmanID_1 = SM1.SalesmanID
RIGHT OUTER JOIN tblSalesMan SM2
ON Sales.SalesmanID_2 = SM2.SalesmanID
RIGHT OUTER JOIN tblSalesMan SM3
ON Sales.SalesmanID_3 = SM3.SalesmanID
RIGHT OUTER JOIN tblSalesMan SM4
ON Sales.SalesmanID_4 = SM4.SalesmanID;
Does that help?
- 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
April 17, 2009 at 3:01 pm
Thanks..That did work up to the point where the sales commision is split greater than 3 ways. If the coulumn salemanId_3 and salesmanId_4 are Null, then it returned all records as Null. I would love to redsigned the tables but they are integrated in a propriatary COBAL application. Yup..that was COBAL.
April 17, 2009 at 3:05 pm
Try changing them to Left Outer joins instead of Right Outer. I confused myself by rewriting it a couple of times.
- 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
April 17, 2009 at 3:15 pm
Excellent. Thanks that worked.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply