Query

Question

Hi,

I have three tables schedule (schID, courseiD, sdate, edate), instructorschedule(instructorID, schID), instructors(instructorID, instructorName) schedule and instructorSchedule linked using schID, instructors and instructorschedule linked with instructorid.

two instructor may have same schid.

I need to retrieve instructornames (all instructor that have same schid), start date, and enddate.

the output something like this:

(Mohammed, Jone, Mike, Ahmed) (2012-09-12) (2012-12-12)

the problem is that I use CTE but I got this message (Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.)

Thanks in advance.

---

Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid

MCTS: SQL Server Administration/Development

MyBlog

Answer 1

Did you try to have MAXRECURSION in your query?

It would be great if you could share us the sample data and the code that you have been using to help you better.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 2

Hi

use MAXRECURSION 0 query hint.

Please read this

http://msdn.microsoft.com/en-us/library/ms181714.aspx

http://msdn.microsoft.com/en-us/library/ms175972.aspx 

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 3

Thanks all for your quick replay.

But the problem is not with the maxrecursion only the query result isn't as expected I want to concatenate all instructors that in the same schedule and retrieve their names like this (instructor1name, instructor2name, ....).

---

Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid

MCTS: SQL Server Administration/Development

MyBlog

Answer 4

Hi,

In order to concatentate all the names, you'll need to use a function.  I think this will work for you:

CREATE FUNCTION dbo.instructorString 
( 
    @schID int 
) 
RETURNS VARCHAR(max) 
AS 
BEGIN 
    DECLARE @r VARCHAR(max) 
    SELECT @r = ISNULL(@r+',', '') 
        + InstructorName 
    FROM 
		instructors as i
	inner join
		instructorsSchedule as iSch on isch.instructorID = i.instructorID
    WHERE isch.schid = @schID 
    RETURN @r 
END 
GO 


select
	instructorString(schid) AS Instructors, sdate, edate
from
	schedule

Regards
James

---

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 5

you can try to use XML functions available in SQL. I guess there you will have space options.

regards

joon

Answer 6

>> I have three tables  <<

And you could be bothered to post DDL for them? Does your teacher make you code without any specs or DDL? Now we have to guess about keys, data types, DRI and everything else to do your job for you. 

>> schedule and instructor_schedule linked using sch_id, instructors and instructor_schedule linked with instructor_id. <<

SQL does not have links; that was network databases which used pointer chains. We have referenced and referencing tables. Is this a homework assignment and you are so new you do not know what DDL is or the basic terms of RDBMS? Here is my guess: 

CREATE TABLE Schedules
(sch_id INTEGER NOT NULL PRIMARY KEY, 
 course_nbr CHAR(5) NOT NULL, 
 course_start_date DATE NOT NULL, 
 course_end_date DATE NOT NULL,
 CHECK (course_start_date < course_end_date)),

CREATE TABLE Instructors
(instructor_id CHAR(10) NOT NULL, 
 instructor_name VARCHAR(25) NOT NULL);

CREATE TABLE Instructor_Schedule
(instructor_id CHAR(10) NOT NULL
  REFERENCES Instructors (instructor_id), 
 sch_id INTEGER NOT NULL
  REFERENCES Schedules (sch_id),
 PRIMARY KEY(instructor_id, sch_id));

>> I need to retrieve instructor_names (all instructor that have same sch_id), start date, and end date. The output something like this:
(Mohammed, Jones, Mike, Ahmed) (2012-09-12) (2012-12-12) <<

That would violate First Normal Form (1NF). Do you know what Normal Forms are? Display formatting is done in the presentation layers, not in the DML. If you do not care about beign a good SQL programmers, I am sure other people will give you the XML and recursive CTE kludges for this common programming error. 

>> the problem is that I use CTE but I got this message (Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.) <<

Where is this code? We have to guess at everything for you. Here is the basic query you pass to the front end: 

SELECT I.instructor_name, S.course_start_date, S.course_end_date
  FROM Schedules AS S,
       Instructors AS I,
       Instructor_Schedule AS IS
 WHERE IS.sch_id = C.sch_id
   AND IS.instructor_id = I.instructor_id

ORDER BY S.course_start_date, S.course_end_date, I.instructor_name;

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 7

if you're using SQL Server 2005 or above, use XML PATH('') solution for string concatenation if you want to do it server side (better to do it client side).

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog