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