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