Convert select statement

[saintedmunds]

HI

I am Coverting from an access mdb to sql 2005 and I have the below select staement which i seem unable to convert.

SELECT tTimeTable.BoxID, [Subject] & Chr(13) & Chr(10) & Concatenate("SELECT tStaff.StaffInitials FROM tTimeTable INNER JOIN tStaff ON tTimeTable.StaffID = tStaff.StaffID WHERE tTimeTable.BoxID=" & tTimeTable.BoxID & " AND tTimeTable.DayID=1") AS SubjectStaff, tTimeTable.DayID
FROM tTimeTable LEFT JOIN tSubject ON tTimeTable.SubjectID = tSubject.SubjectID
WHERE (((tTimeTable.DayID)=1));


Any help would be great

Cheers

 

[bborissov]

Try:

SELECT tTimeTable.BoxID,
       [Subject] + Char(13) + Char(10) +
       'SELECT tStaff.StaffInitials FROM tTimeTable
               INNER JOIN tStaff ON tTimeTable.StaffID = tStaff.StaffID
               WHERE tTimeTable.BoxID=' +
                     CAST(tTimeTable.BoxID as varchar(20)) + ' AND  tTimeTable.DayID=1' AS SubjectStaff,
       tTimeTable.DayID
FROM tTimeTable
      LEFT JOIN tSubject ON tTimeTable.SubjectID = tSubject.SubjectID
WHERE tTimeTable.DayID=1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[saintedmunds]

Hi

Thank you for getting back to me so quick.
But the result comes out like this:

Maths
SELECT tStaff.StaffInitials FROM tTimeTable
INNER JOIN tStaff ON tTimeTable.StaffID = tStaff.StaffID
WHERE tTimeTable.BoxID=1 AND tTimeTable.DayID=1

Any Ideas?

Cheers

 

[bborissov]

How do you want the result to looks like?
If you want the SELECT to be on one row just change it to be on one row:

[Subject] + Char(13) + Char(10) +
'SELECT tStaff.StaffInitials FROM tTimeTable INNER JOIN tStaff ON tTimeTable.StaffID = tStaff.StaffID WHERE tTimeTable.BoxID=' + CAST(tTimeTable.BoxID as varchar(20)) + ' AND  tTimeTable.DayID=1' AS SubjectStaff

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[saintedmunds]

Hi

Sorry maybe I didnt make my self clear.

The reults im getting is actually display the select statement when i should get something like this

BoxID SubjectStaff DayID
6 English 1
HBP, IvO

Where as im getting

BoxID SubjectStaff DayID
6 SELECT tStaff.StaffInitials FROM tTimeTable
INNER JOIN tStaff ON tTimeTable.StaffID = tStaff.StaffID
WHERE tTimeTable.BoxID=1 AND tTimeTable.DayID=1

Do you see what i mean?

 

[bborissov]

OOOOOOOOO , now I understand
Try:

SELECT tTimeTable.BoxID,
       [Subject] + Char(13)+Char(10)+
       Tbl1.StaffInitials AS SubjectStaff,
       tTimeTable.DayID
FROM tTimeTable
     LEFT JOIN tSubject ON tTimeTable.SubjectID = tSubject.SubjectID
     LEFT JOIN (SELECT tStaff.StaffInitials, tTimeTable.BoxID
                       FROM tTimeTable 
                       INNER JOIN tStaff ON tTimeTable.StaffID = tStaff.StaffID
                       WHERE tTimeTable.DayID=1) Tbl1
     ON tTimeTable.BoxId = Tbl1.BoxID
WHERE tTimeTable.DayID=1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[saintedmunds]

Hi

We are almost there but it is only picking up one staff initial somtimes there is more than one that is why i had to use the Concatenate function.

The initals are seperated by a ,

Any ideas?

Cheers again

 

[bborissov]

No, In TSQL you didn't have Concatenate function. If you want to Concatenate read FAQ183-6466 to see how to do it.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP