Select mulitple ints - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select mulitple ints

Author

Topic

hendribt
Starting Member

4 Posts

Posted - 2005-07-12 : 15:14:27

To make this simple I need to select multiple ints from a table where another field is null. I then need to use these ints to select multiple other ints from another table that corresonde to these. And again I need to select another series of ints from another table that correspond to the previous set. What is the easiest way of doing this?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-12 : 15:27:36

I thought you said "to make this simple" :)

how about this?


select	whateverYouWantFromTablesABorC
from	aTable a
join	anotherTable b
		on a.intCol = b.intCol
join	andAgainTable c
		on b.intCol = c.intCol
where	a.anotherField is null

Be One with the Optimizer
TG

hendribt
Starting Member

4 Posts

Posted - 2005-07-12 : 15:40:51

Ok well maybe this isn't that simple, basically what I am trying to accomplish at the end is to fill in those null fields. I have an employeerelationship table that creates a relationship between and employee(childID) and their supervisor(ParentID). Some of the parentID fields will be null because when I first populate the employee table an employee's manager may not be inserted yet. Thus when the relationship gets created a null value is entered in the parentID field. So I wanted to go back find all these null values, find their corresponding employees. Then find their corresponding supervisor. Get the supervisor's ID and insert that into the parentID. The employee table contains the primary key(identifier) which is created by SQL. It also contains a company ID that is unique to each employee, and corresponding supervisorID. The relationship tables links the supervisor and employee based on the identifier not the company ID. So I link the employee together based on the company ID but then create the relationship using the identifer.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-12 : 16:00:02

It's really not a good idea for a relationship table to allow nulls for the entities they are relating.
<sarcasm>Typically, a record won't be created until there is an actual relationship.</sarcasm>

However, if this doesn't solve your issue, post your actual DDL with sample DML and expected results:


set nocount on
declare @employee table (employeeid int, supervisorid int)
declare @employeeRelationship table (parentid int, childid int)

insert @employee 
select 1, null union
select 2,1 union
select 3,2 union
select 4, null union
select 5, 4 union
select 6, 2

insert @employeeRelationship 
select 1,2 union
select 2,3 union
select 4,5 union
select null, 6

select * from @employee

print 'pre update'
select * from @employeeRelationship

update	a set
	a.parentid = b.supervisorid
from	@employeeRelationship a
join	@employee b
		on a.childid = b.employeeid

print 'post update'
select * from @employeeRelationship

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources