MS SQL

Sql Query

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Oct 2009

Posts: 7

Reputation: Moody1 is an unknown quantity at this point

Solved Threads: 0

Moody1

Offline

Newbie Poster

Sql Query

Oct 26th, 2009

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

http://img143.imageshack.us/img143/6704/13503390.th.jpg

and i want a query which return Emp_ID, Pro_ID

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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')
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

Last edited by peter_budo; Oct 27th, 2009 at 10:01 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

•

Join Date: Feb 2009

Posts: 3,443

Reputation: sknake has much to be proud of

Solved Threads: 626

sknake

Offline

.NET Enthusiast

Oct 27th, 2009

If the tables have a 1:1 relationship:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Oct 2009

Posts: 7

Reputation: Moody1 is an unknown quantity at this point

Solved Threads: 0

Moody1

Offline

Newbie Poster

Oct 27th, 2009

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?

•

Join Date: Feb 2009

Posts: 3,443

Reputation: sknake has much to be proud of

Solved Threads: 626

sknake

Offline

.NET Enthusiast

Oct 27th, 2009

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.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Oct 2009

Posts: 7

Reputation: Moody1 is an unknown quantity at this point

Solved Threads: 0

Moody1

Offline

Newbie Poster

Oct 27th, 2009

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

•

Join Date: Feb 2009

Posts: 3,443

Reputation: sknake has much to be proud of

Solved Threads: 626

sknake

Offline

.NET Enthusiast

Oct 27th, 2009

>>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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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)
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)

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Oct 2009

Posts: 7

Reputation: Moody1 is an unknown quantity at this point

Solved Threads: 0

Moody1

Offline

Newbie Poster

Oct 27th, 2009

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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')
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

Last edited by peter_budo; Oct 27th, 2009 at 10:02 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

Attached Thumbnails