| Author |
 Topic  |
|
|
helixpoint
Constraint Violating Yak Guru
291 Posts |
 Posted - 12/04/2013 : 10:32:51
|
I am sorry for the brevity, but because of where I work, I can only put this up and I am limited. I can't figure out the SQL
I have 2 tables:
--recordCrossSystem
ID
RecordID
CrossSysID
--Record
ID
RecordNum
recordNum
Title
recordCrossSystem Example
1 100 110
2 100 111
3 100 112
Record Example
1 100 12345 Record 1
2 110 12346 This is the cross system record of 100
3 111 12347 This is the cross system record of 100
4 112 12348 This is the cross system record of 100
So in my application I need to bring back all the records that go with the cross system, but I have to search on recordNum. So if I search for 12348. I have to bring back
1 100 12345 Record 1
2 110 12346 This is the cross system record of 100
3 111 12347 This is the cross system record of 100
4 112 12348 This is the cross system record of 100
and if I search for 12345 which is the parent record. I have to bring back the same
1 100 12345 Record 1
2 110 12346 This is the cross system record of 100
3 111 12347 This is the cross system record of 100
4 112 12348 This is the cross system record of 100
Dave
Helixpoint Web Development
http://www.helixpoint.com |
|
|
helixpoint
Constraint Violating Yak Guru
291 Posts |
Posted - 12/04/2013 : 13:02:53
|
declare @recordCrossSystem table
(ID int, RecordID int, CrossSysID int);
declare @record table
(ID int, CrossSysID int, RecordNum int, Title varchar(50));
insert into @recordCrossSystem select 1,100,110;
insert into @recordCrossSystem select 2,100,111;
insert into @recordCrossSystem select 3,100,112;
insert into @record select 1,100,12345,'Record 1';
insert into @record select 2,110,12346,'This is the cross system record of 100';
insert into @record select 3,111,12347,'This is the cross system record of 100';
insert into @record select 4,112,12348,'This is the cross system record of 100';
declare @RecordNum int;
set @RecordNum = 12348;
select distinct r2.ID, r2.CrossSysID, r2.RecordNum, r2.Title, rcs2.RecordID
from (
select coalesce(rcs.RecordID, r.CrossSysID) as RecordID
from @record r
left join @recordCrossSystem rcs on r.CrossSysID = rcs.CrossSysID
where r.RecordNum = @RecordNum
) baserecord
join @recordCrossSystem rcs2 on baserecord.RecordID = rcs2.RecordID
join @record r2 on r2.CrossSysID = rcs2.CrossSysID or r2.CrossSysID = rcs2.RecordID;
set @RecordNum = 12345;
select distinct r2.ID, r2.CrossSysID, r2.RecordNum, r2.Title, rcs2.RecordID
from (
select coalesce(rcs.RecordID, r.CrossSysID) as RecordID
from @record r
left join @recordCrossSystem rcs on r.CrossSysID = rcs.CrossSysID
where r.RecordNum = @RecordNum
) baserecord
join @recordCrossSystem rcs2 on baserecord.RecordID = rcs2.RecordID
join @record r2 on r2.CrossSysID = rcs2.CrossSysID or r2.CrossSysID = rcs2.RecordID;
Dave
Helixpoint Web Development
http://www.helixpoint.com |
 |
|
|
Natalia89
Starting Member
Poland
12 Posts |
Posted - 12/04/2013 : 16:21:21
|
I have a table that contains empid, name, salary, hiredate, positionand supervisor (which includes empid not the name) how do i list the empid and name of all supervisors ? the output has to have to columns supervisor( and a list of their emid) and their names. Thats the create statement used to create the employee table:
/* Create table Employee */
IF OBJECT_ID('Employee', 'U') IS NOT NULL
DROP TABLE Employee
GO
CREATE TABLE Employee (
emp_id NCHAR(5),
name NVARCHAR(20),
position NVARCHAR(20),
hire_date DATETIME,
salary MONEY,
bcode NCHAR(3),
supervisor NCHAR(5)
)
i have tried a variety of statements using having statement and count but the dont seem to work:/
select emp_id, name from employee where position='manager'; i tried this but it doesnt work anoune smart that knows how to do it?
saddf |
|
|
|
Natalia89
Starting Member
Poland
12 Posts |
Posted - 12/04/2013 : 16:22:40
|
anyone please help? i am trying to get this statement but nothing i come up with works
saddf |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
6065 Posts |
|
| |
Topic |
|
|
|
Sql Baffeled