July 7, 2011 at 7:56 pm
Hi All,
In the query below to get the name of the employee(salesofficer, manager) i have called EMaster table twice. Is there anyother way to display both the names of salesofficer and manager with joining the Emaster table just once. If so, will the new solution be efficient than the current one.
NB: I'm using sql server 2005
select b.salesamount, a.eName [Sales Officer], c.eName[Manager]
from SalesData b
inner join EMaster a on a.sid = b.salesofficerid -- this was corrected to its current form from 'inner join
--EMaster a on a.sid = b.sdid' after Nevyn pointed out.
inner join EMaster c on c.sid = b.managerid
Below is the code for test purpose:
create table EMaster
(
sid int identity(1,1),
eName varchar(10),
eRole varchar(10)
)
insert into EMaster
select 'soa', 'SO' union all
select 'soc', 'SO' union all
select 'sox', 'SO' union all
select 'xma', 'MNGR' union all
select 'xmx', 'MNGR' union all
select 'so1', 'SO' union all
select 'soq', 'SO'
--select * from Emaster
create table SalesData
(
sdid int identity(1,1),
salesofficerid int,
managerid int,
salesamount decimal(11,2)
)
insert into SalesData
select 1, 4, 5700140 union all
select 7, 5, 7000000 union all
select 6, 5, 5800000 union all
select 2, 4, 9800000 union all
select 1, 4, 5700000
--select * from SalesData
Thanks for guidance 🙂
Sree
July 7, 2011 at 10:14 pm
Is the query above correct? Why would you link the tables on the two identity rows?
Was that meant to be "inner join EMaster a on a.sid = b.salesofficerid"??
Anyway, the answer to your question is no you can't join just once (you need data from two different rows of EMaster to be linked to a single row of your other table).
And no, it shouldn't be a performance concern as long as the clustered index on eMaster is sid.
July 7, 2011 at 10:43 pm
Hi Nevyn,
Thanks for the guidance.
//Was that meant to be "inner join EMaster a on a.sid = b.salesofficerid"??
Yes. Thanks for pointing it out 🙂 . I've corrected it
July 8, 2011 at 12:42 am
- create primary keys for your tables and foreign keys to support your data integrity !
( very nice ref on how SQLServer takes advantage of this: Do Foreign Key Constraints Help Performance?[/url] by Grant Fritchey. )
- double check your indexes !
another question may be: What is "managerid" doing in the sales detail table, why is there no date/datetime indication in that detail row ?
Of course one could respond "denormalized", but that should be a well chosen decision. :unsure:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply