Sql Query

[Moody1]

Hello,
Ihope to find some help in here, i have the following diagram

[IMG]http://img143.imageshack.us/img143/6704/13503390.th.jpg[/IMG]

and i want a query which return Emp_ID, Pro_ID

note;
i can get these values but by 2 queries as the following;

select Pro_ID from Projects where Dep_ID in 
(select Dep_ID from Departments where name = 'test') 

select Emp_ID from Employees where Dep_ID in 

(select Dep_ID from Departments where name = 'test')

the goal of having the result through one query is that i want to insert the value into another table
insert into testTable (query)

Hope to see reply ASAP

[sknake]

If the tables have a 1:1 relationship:

Select
(
  select Top 1 Pro_ID 
  from Projects 
  where Dep_ID in 
  (
    select Dep_ID 
    from Departments 
    where name = 'test'
  ) 
) As PRO_ID,
(
  select Top 1 Emp_ID 
  from Employees 
  where Dep_ID in 
  (
    select Dep_ID 
    from Departments 
    where name = 'test'
  )
) As EMP_ID

[Moody1]

Hello,
Thanks for ur quick response.
the relationship is 1:m
so i'm getting this error message when i'm trying this query
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

i think that i need to use JOIN but i don't know how to join these tables to get the desire results.

so any other suggestions?

[sknake]

You haven't provided enough information to answer that question. Please provide table structures for both tables, sample data, and desired results from your sample data.

[Moody1]

the structure is as the follow;
Departments
Dep_ID (int), Name (Varchar)
Employee
Emp_ID (int), Name (Varchar), Dep_ID (int)
Projects
Pro_ID (int), Name (Varchar), Dep_ID (int)
Pro_Emp
ID (int), Pro_ID (int), Emp_ID (int)

sample data
Departments
1 ------- Administration
2 ------- Markiting
Employee
1 ------- Mike ------ 2
2 ------- jhone ----- 1
Projects
1 ------- test ------ 2
2 ------- test1 ----- 1

Now i want to get Emp_ID, Pro_ID which have the same Dep_ID depending on the Dep name and insert them into temp table and then insert them back to Pro_Emp table

the relationships are clarified in the attached pic up in the first post.

i hope that the situation is clear now

[sknake]

>>the relationships are clarified in the attached pic up in the first post.
That picture is a 1x1" square. There is no way you could read it..

Try something like this. I still don't understand what you're asking but maybe this will work:

IF OBJECT_ID('tempdb..#Department', 'U') IS NOT NULL DROP TABLE #Department
IF OBJECT_ID('tempdb..#Employee', 'U') IS NOT NULL DROP TABLE #Employee
IF OBJECT_ID('tempdb..#Projects', 'U') IS NOT NULL DROP TABLE #Projects

Create Table #Department
(
  Dep_ID int PRIMARY KEY,
  Name varchar(50)
)
Insert Into #Department (Dep_ID, Name) Values (1, 'Administration')
Insert Into #Department (Dep_ID, Name) Values (2, 'Marketing')

Create Table #Employee
(
  Emp_ID int PRIMARY KEY,
  Name varchar(50),
  Dep_ID int
)
Insert Into #Employee (Emp_ID, Name, Dep_ID) Values (1, 'Mike', 2)
Insert Into #Employee (Emp_ID, Name, Dep_ID) Values (2, 'Jhone', 1)

Create Table #Projects
(
  Pro_ID int PRIMARY KEY,
  Name varchar(50),
  Dep_ID int
)
Insert Into #Projects (Pro_ID, Name, Dep_ID) Values (1, 'test', 2)
Insert Into #Projects (Pro_ID, Name, Dep_ID) Values (2, 'tes1', 1)

Select *
From #Department Inner Join #Projects On (#Projects.Name = 'Test' and #Department.Dep_ID = #Projects.Dep_ID)
                 Inner Join #Employee On (#Projects.Dep_ID = #Employee.Dep_ID)

[Moody1]

about the pic i've attached another one, i hope it's clear now.
i will describe the process which will get me to the desired result
- i have projects on the waiting list in this table
Waiting_List
ID (int)
Name (varchar)
Dep_ID (int)

now after moving the projects from the waiting_list into their table Projects i want to fill Pro_Emp table which used to broke many to many relationship between Projects and Employee table.
so the main goal is to fill Pro_Emp table

so your first query is giving me the desired results but in the case of 1:1 relationship.

the insert query should be like this

insert into Pro_Emp (Emp_ID, Pro_ID) select Emp_ID, Pro_ID from Employee, Projects where Dep_ID in (select Dep_ID from departments where name = 'test')

(this query isn't right but i've put it as sample to the query which i want)
as i said before i can get Emp_ID individually by a query but i want to get these two values togethr (Emp_ID, Pro_ID)

i hope this clear now, if not please let me know the point which making u confused.

thanks for ur help

[sknake]

Take the query I updated, add more test data, and report back what you want. The test data you provided only had a 1:1 relationship in the database.

[Moody1]

Hello,
i just wanna say many thanks for ur help
i've solved it.