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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -