Sql Baffeled - 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 2012 Forums

Transact-SQL (2012)

Sql Baffeled

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-12-04 : 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 - 2013-12-04 : 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

12 Posts

Posted - 2013-12-04 : 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

12 Posts

Posted - 2013-12-04 : 16:22:40

anyone please help? i am trying to get this statement but nothing i come up with works

saddf

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-04 : 16:42:43

Natalia89, please don't hijack other threads and you only need to post your question once. Chillax dude!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190089

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources