Need help on an sql query. [Resolved]

Posted by A4u_6178 under Sql Server on 7/10/2011 | Points: 10 | Views : 1252 | Status : [Member] | Replies : 9
Hi all,
I have 2 tables department(with columns depID and Departmentname)and employee(with columns EmpID,Emp_name,DepID1,DepID2).
I resultant table as
EmpID,Emp_name,Departmentname,Departmentname
i need the query which gives me above resultant table.

Thanks & Regards,


Responses

Posted by: Umeshdwivedi on: 7/10/2011 [Member] Starter | Points: 50

Up
0
Down

Resolved
This is the most basic SQL you could ever write. It's called an inner join, where you join two tables together by using a value common to both, in this case, the depID. As you have two tables, you need to join twice, as in
select E.EmpID, E.Emp_Name, d1.DepartmentName, d2.DepartmentName from Employee e inner join department d1 on e.DepID1 = d1.DepID inner join department d2 on e.depID2 = d2.DepID


Latest Technology Trainer
And Part time software consultant

A4u_6178, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Umeshdwivedi on: 7/10/2011 [Member] Starter | Points: 25

Up
0
Down
hallo
you can use alias
like this
Select e.name,e.empid,d.deptno,d.deptname from emp e,department d where d.deptno=e.deptno

Latest Technology Trainer
And Part time software consultant

A4u_6178, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A4u_6178 on: 7/10/2011 [Member] Starter | Points: 25

Up
0
Down
Hi umesh,
thanks for ur reply, what i want is a result which is similar to employee table but the dep_id1, dep_id2 should replaced wit respective departmentname from department table.
Suppose an employee belongs to 2 departments then
i need the result as
empid,empname,departmentname, departmentname

Thanks & Regards,

A4u_6178, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Umeshdwivedi on: 7/10/2011 [Member] Starter | Points: 25

Up
0
Down
hallo
you send a snap of ur both table
here i cant understand ur query
i think you have two table emp and dept.
you want to show both data column with both table i m right
and you say that one employee belongs two deptment here i cant understand
supose if one employee belongs two account deptment and development department
so what you want pls specify first then i give your solution

Latest Technology Trainer
And Part time software consultant

A4u_6178, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A4u_6178 on: 7/10/2011 [Member] Starter | Points: 25

Up
0
Down
Dear sir,
Thank you, the query u provided working fine...!
If there is any other method to achieve d same result then pls let me know..

Thanks & Regards,

A4u_6178, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Umeshdwivedi on: 7/11/2011 [Member] Starter | Points: 25

Up
0
Down
HALLO
IF MY ANSWER HELP YOU THEN MARK AS ANSWER
AND YOU ASK AGAIN ANY QUESTION MOST WELCOME

Latest Technology Trainer
And Part time software consultant

A4u_6178, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 7/12/2011 [Member] Starter | Points: 25

Up
0
Down
select e.empid,e.empname,d.departmentname,d.deptid from emp e,department d where d.deptid in(e.deptid1,e.deptid2)



Thanks&Regards
LakshmiNarayana Nalluri.

A4u_6178, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A4u_6178 on: 7/12/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Lakn2,
the query u have provided returns the results correctly(but not in the format which em looking for), my requirement is to get the result as the innerjoin query( refer above responses) returns.
Thanks for reply.

Thanks & Regards,

A4u_6178, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: RaviRanjanKr on: 7/12/2011 [Member] Starter | Points: 25

Up
0
Down
Its better for you to learn JOIN Fundamental in SQL server.
given link let you to learn it step by step. so navigate now
http://dotnetslackers.com/articles/sql/SQL-SERVER-JOINs.aspx
http://msdn.microsoft.com/en-us/library/ms191517.aspx

Thanks
Ravi Ranjan Kumar
http://raviranjankr.wordpress.com

A4u_6178, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response