March 5, 2013 at 9:18 pm
Hai friends,
create table user
(
user_id varchar(100),
name varchar(100),
designation_id varchar(100),
grade_id varchar(100)
)
insert into user values('0012','abc',13,8)
insert into user values('0010','bc',5,3)
insert into user values('0011','bjc',2,3)
insert into user values('0013','bct',6,3)
insert into user values('0016','bci',59,35)
insert into user values('0019','bcp',9,11)
create table designation
(
desigantaion_id varchar(100),
name varchar(100),
grade_id varchar(100)
)
insert into designation values('13','progrmmer',8)
insert into designation values('5','GM','3')
insert into designation values('2','regional manager','3')
insert into designation values('6',' accounts manager','3')
insert into designation values('59','worker','35')
insert into designation values('9','trainee','11')
create table sal
(
effective_date date,
sala varchar(100),
designation_id varchar(100)
grade varchar(100)
)
insert into sal values('2010-01-01','5000','13','b2')
insert into sal values('2010-01-01','10000','5','a1')
insert into sal values('2010-01-01','10000','2','a1')
insert into sal values('2010-01-01','10000','6','a1')
insert into sal values('2010-01-01','2000','59','e')
insert into sal values('2010-01-01','3000','9','c')
insert into sal values('2011-01-01','5500','13','b2')
insert into sal values('2011-01-01','11000','5','a1')
insert into sal values('2011-01-01','11000','2','a1')
insert into sal values('2011-01-01','11000','6','a1')
insert into sal values('2012-03-01','2500','59','e')
insert into sal values('2012-02-01','3600','9','c')
insert into sal values('2012-01-01','15000','6','a1')
now i wanna make a join all the table,if i ' m pass the effective_date depends on the salry ll display all the employes....
such designations are missed on the '2012-01-01' in that... if i m pass the all effective _dates the outputs of sal shows '2010' and '2012','2011' also that each employess sal display three times
i waana display the occurate sal of all employees depends on effective_dates.
March 6, 2013 at 1:01 am
Please provide expected output too here with respect to the any effective date (from you sample data)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 6, 2013 at 7:08 am
Your table definitions contain serious errors. Only way to join users (give the correct datatype and references are used) is by joining sals with users on designation_id.
This happens to be a cross join (Example for user '0012'):
user_id, effective_date, sala, user.designation_id, sal.designation_id, user.grade_id, sal.grade
'0012', '2012-01-01', '5000', '13', '13', '8', 'b2'
'0012', '2011-01-01', '5500', '13', '13', '8', 'b2'
'0012', '2012-01-01', '5000', '13', '13', '8', 'b2'
You also might duplicate values when using designation table to join user and sal.
Work on your table design!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy