Need help on an sql query. [Resolved]

Posted by A4u_6178 under Sql Server on 7/10/2011 | Points: 10 | Views : 2322 | Status : [Member] | Replies : 9

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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,

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

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

0	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

0	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

0	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

0	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

0	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

Latest Posts