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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Sql Baffeled
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 12/04/2013 :  10:32:51  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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

Poland
12 Posts

Posted - 12/04/2013 :  16:21:21  Show Profile  Reply with Quote
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

Poland
12 Posts

Posted - 12/04/2013 :  16:22:40  Show Profile  Reply with Quote
anyone please help? i am trying to get this statement but nothing i come up with works

saddf
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 12/04/2013 :  16:42:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000