| Author |
Topic |
|
iktheone
Yak Posting Veteran
66 Posts |
 Posted - 2005-10-20 : 07:08:50
|
| --Table Empmaster:-create table Empmaster(empid int identity(1,1) constraint pkempid primary key clustered,empname varchar(10),empsalary numeric)insert Empmaster(empname,empsalary)values('Imran',5000)insert Empmaster(empname,empsalary)values('Raja',5000)--Table Salary:-create table Salary(salid int identity(1,1) constraint pksalid primary key clustered,empid int constraint fkempid foreign key references Empmaster(empid),dos varchar(10),salary numeric) insert Salary(empid,dos,salary)values('1','2005-08-01','5000')insert Salary(empid,dos,salary)values('2','2005-08-01','5000')insert Salary(empid,dos,salary)values('1','2005-09-01','5000')insert Salary(empid,dos,salary)values('2','2005-09-01','7000')insert Salary(empid,dos,salary)values('1','2005-10-01','7000')insert Salary(empid,dos,salary)values('2','2005-10-01','7000')i have two tables with relations. how do i find out whose salary has been hiked for the 9th month? |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-10-20 : 07:17:20
|
| somthing like this .. Select a.* From EmpMaster a Inner join Salary b on a.EmpID = b.EmpidAnd a.EmpSalary<> b.SalaryWhere Month(Dos) = 9Complicated things can be done by simple thinking |
 |
|
|
iktheone
Yak Posting Veteran
66 Posts |
Posted - 2005-10-20 : 07:37:41
|
| there is something wrong with the output.the output according to your query is:empid empname salary2 Raja 5000the output should be:empid empname salary2 Raja 7000 |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-10-20 : 07:59:52
|
| Select a.EmpID,a.EmpName,b.Salary From EmpMaster a Inner join Salary b on a.EmpID = b.EmpidAnd a.EmpSalary<> b.SalaryWhere Month(Dos) = 9Complicated things can be done by simple thinking |
|
|
|
iktheone
Yak Posting Veteran
66 Posts |
Posted - 2005-10-20 : 08:33:26
|
| there is one more problem. suppose a new employee joins in the 2nd month named 'Vijay'. lets say his salary is 8000 when he joins. and his salary is incremented in the 3rd month. in that case the above query will be wrong if i ask hiked salary for the 3rd month! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-10-20 : 08:56:14
|
>> in that case the above query will be wrong if i ask hiked salary for the 3rd month!Of course it would be "wrong", it was written as specified. You said it should return salary changes in the 9th month, then you complain that it won't work if the salary change is in the 3rd month?? Glad you're not my boss!  Some advice:1) "Salary" doesn't belong in the EmpMaster table. In addition, you should ALWAYS use proper datatypes; your dates should be using DateTime and not VarChar(). (Don't say you just used varchar for this sample; it is a bad habit to get into and can greatly affect not only the efficiency but also the accuracy of solutions)2) You need to step back and think about what you need from a more logical perspective, that will be more flexible. Never think in terms of "I need a SELECT that returns total sales for Jan 2004", think "I need a SELECT that returns total sales per year/month".3) You might want to consider a PK in your salary table of a comination of EmpID/Dos. There should never be two entries on a single day for a single employee, so that should be your primary key. If you don't have that constraint, almost any SQL statement that you try to write to use this salary data may not return consistent or accurate results if you ever accidentally have two entries on the same day for an employee. |
|
|
|
iktheone
Yak Posting Veteran
66 Posts |
Posted - 2005-10-20 : 09:05:55
|
My god u sound just like my TL! And u talk just like Smith from the movie MATRIX!  Anyway ill do the needful and get back to you. |
|
|
|
iktheone
Yak Posting Veteran
66 Posts |
Posted - 2005-10-20 : 09:44:24
|
| 3) You might want to consider a PK in your salary table of a comination of EmpID/Dos. There should never be two entries on a single day for a single employee, so that should be your primary key. If you don't have that constraint, almost any SQL statement that you try to write to use this salary data may not return consistent or accurate results if you ever accidentally have two entries on the same day for an employee.But i didnt understand this! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-10-20 : 10:04:01
|
quote:
Originally posted by iktheone
3) You might want to consider a PK in your salary table of a comination of EmpID/Dos. There should never be two entries on a single day for a single employee, so that should be your primary key. If you don't have that constraint, almost any SQL statement that you try to write to use this salary data may not return consistent or accurate results if you ever accidentally have two entries on the same day for an employee.But i didnt understand this!
PK = primary key. A primary key can be more than 1 column in a table, and your primary key indicates which column(s) must have unique values for each row in your table.Before doing much of any work with SQL Server, be sure to read up and understand normalization:http://www.datamodel.org/NormalizationRules.htmlToo many consultants/beginners/Team Leads think that a primary key must be an identity column, and that they are one and the same, and don't understand data modelling at all. |
|
|
|
iktheone
Yak Posting Veteran
66 Posts |
Posted - 2005-10-20 : 10:23:43
|
| Server: Msg 8110, Level 16, State 1, Line 1Cannot add multiple PRIMARY KEY constraints to table 'Salary'.but when i try to create two primary key columns in the same table i get this error message! |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-10-24 : 11:35:38
|
| "....but when i try to create two primary key columns in the same table i get this error message!"What code are you using to 'attempt this'....please post. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-24 : 13:39:38
|
| This is when I want to take up gardening and leave this business... |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-10-25 : 02:53:48
|
| Hey Rockmoose, try bus driving, round here it's better paid AND you can see who's stabbing yousteve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-25 : 11:20:01
|
quote:
Originally posted by elwoos
Hey Rockmoose, try bus driving, round here it's better paid AND you can see who's stabbing yousteve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true!
*LOL*Good idea, but I may need to get an education first... |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-10-26 : 02:59:12
|
| If you can't count and can't drive you'll be fine -----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-26 : 03:07:33
|
| |
|
|
|
|
Hiked Salary...