SQL Server - Pls help with these sql query

Asked By Sumit Kumar on 02-Feb-10 10:43 AM
Hi all,
Pls give me query of these following SQL problem.
1. see the below table with the data in it. Make the query to fetch the user name who involved in Banking and Finance projects. 

Table name : projectdetails

user        project  ----- column names

venki       Banking
Raju        Admin
venki       Finance
kishore     Finance
Raju        Insurance
kishore     Insurance
kiran       Banking

output should be - venki
2. see the below table with the data in it. Here the managerid is also one of the employee id. Make the query to display empid, empname and managername accordingly

Table name : Employee

Empid     Empname    Managerid   ---- column names
1             venki            3 
2             sekhar      
3             suresh          4     
4             Bhaskar       6
5             Sudhakar      6
6            Janardhan     2

Output sould be in the format below

Empid    Empname  Managername
1               venki         suresh
2              sekhar
3              suresh        Bhaskar
4              Bhaskar      Janardhan
5              Sudhakar    Janardhan
6             Janardhan    sekhar

3. see the below table with the data in it

Table name : Numbers

A       B        C   ----column names
1       3        6
2       4        7
2       5        6

output should as below in the descending order 
7
6
5
4
3
2
1

SQL Server CTE

Robbe Morris replied to Sumit Kumar on 02-Feb-10 10:52 AM

this article explains how to do this:

http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp

F Cali replied to Sumit Kumar on 02-Feb-10 11:09 AM

This query is for the first one:

SELECT A.[User]

FROM ProjectDetails A INNER JOIN ProjectDetails B ON A.[User] = B.[User] AND A.Project = 'Banking' AND B.Project = 'Finance'

Regards,
http://www.sql-server-helper.com/tips/sort-ip-address.aspx

Web Star replied to Sumit Kumar on 02-Feb-10 11:12 AM

use this query for your desired result

1.

select user  from projectdetails Where Project ='Banking' and project='Finance'

2.


select E1.Empid, E1.empnamme,E2.empnamme as managername from Employee E1 inner join Employee E2
On E1.Empid = E2.Managerid  

3.

Select A From Numbers

union

Select B From Numbers

union

Select C From Numbers order by 1 desc

Jonathan VH replied to Sumit Kumar on 02-Feb-10 11:21 AM

SELECT [User]
FROM dbo.ProjectDetails
WHERE Project IN('Banking','Finance')
GROUP BY [User]
HAVING COUNT(DISTINCT(Project)) = 2;

SELECT e.EmpID, e.EmpName, ISNULL(m.EmpName,'') AS ManagerName
FROM dbo.Employee e LEFT JOIN dbo.Employee m ON e.ManagerID = m.EmpID;

SELECT A AS x
FROM dbo.Numbers
UNION
SELECT B
FROM dbo.Numbers
UNION
SELECT C
FROM dbo.Numbers
ORDER BY x DESC;

Sumit Kumar replied to Web Star on 02-Feb-10 11:23 AM
Hi,
Your first query i tried it myself before only it doesn't return any value it just display the column name user. I am working with other query...thanks for your reply..