Ad:

MS SQL Discussion Thread
Marked Solved
Views: 594

Oct 26th, 2009

Sql Query

Expand Post »

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;

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)

Similar Threads

help with SQL query / view / count problem.... in MS SQL
Make sure most recent SQL query result is used in autosuggest in PHP
C# VS 2005 - SQL Query Parameters to an ODBC DataSource in C#
sql query updating problem in Visual Basic 4 / 5 / 6
Javascript array from sql query in JSP

Moody1

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Oct 2009

Permalink

Oct 27th, 2009

Re: Sql Query

If the tables have a 1:1 relationship:

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

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Oct 27th, 2009

Re: Sql Query

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?

Moody1

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Oct 2009

Permalink

Oct 27th, 2009

Re: Sql Query

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.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Oct 27th, 2009

Re: Sql Query

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

Moody1

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Oct 2009

Permalink

Oct 27th, 2009

Re: Sql Query

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

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)

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Oct 27th, 2009

Re: Sql Query

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

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

Attached Thumbnails

Click image for larger version

Name: test1.JPG
Views: 14
Size: 32.9 KB
ID: 12321

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)

Moody1

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Oct 2009

Permalink

Oct 27th, 2009

Re: Sql Query

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.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Oct 27th, 2009

Re: Sql Query

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

Moody1

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Oct 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Loop to generate future dates?!

Next Thread in MS SQL Forum Timeline: SSIS Permission Problem

DaniWeb Message

Cancel Changes

User Name
Password