Query Problem selecting records
-
Hi
I need to get info from three tables
I need last course client attended for all clients, result should show course id and date id and client id in any particular order.
But only want one date and course for each user.
Course Details
Course ID
Course Bookings
Course Code
Course Date
client ID
client details
Client ID
Client Details
I need this to work in MS SQL SERVER 2000
Any Help MUCH Appreciated
AB
-
What are the schemas for the three tables? What are the unique/primary constraints for these tables?
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
-
Course Details
Auto number Course ID Primary Key
Course Bookings
auto number course_client_code primary key
long integer Course Code
date Course Date
long integer client ID
client details
auto number Client ID primary key
string Client Details
thanks
if you need anymore info email me on abu_bakar_ayub@hotmail.com
-
What is [Course Code], is it meant to be [Course ID], if so then
SELECT [Client ID],[Course Date],[Course ID]
FROM [Course Bookings] b
INNER JOIN (SELECT [Client ID],MAX([Course Date]) AS [Course Date]
FROM [Course Bookings]
GROUP BY [Client ID]) a
ON b.[Client ID] = a.[Client ID]
AND b.[Course Date] = a.[Course Date]
Can a Client be booked on more than one course for a Course Date?
Far away is close at hand in the images of elsewhere.
Anon. -
yes they can but wont be.
will this work on MS SQL SERVER 2000?
Thanks for the help!!!! Much appreciated
-
will this work on MS SQL SERVER 2000? Yes, but change the first SELECT to
SELECT b.[Client ID],b.[Course Date],b.[Course ID]
(error in my original post)
Far away is close at hand in the images of elsewhere.
Anon. -
what about the second select is it coming from table b?
or a new table a?
thanks
-
No you only need to change the first one. The second select is a derived table (subquery) which I happended to call a.
The query should have been
SELECT b.[Client ID],b.[Course Date],b.[Course ID]
FROM [Course Bookings] b
INNER JOIN (SELECT [Client ID],MAX([Course Date]) AS [Course Date]
FROM [Course Bookings]
GROUP BY [Client ID]) a
ON b.[Client ID] = a.[Client ID]
AND b.[Course Date] = a.[Course Date]
Far away is close at hand in the images of elsewhere.
Anon. -
sorry to keep bothering you.
Getting an error
"ADO error: Invalid Column Name 'Course Date'
statement(s) could not be prepared.
deferred prepare could not be completed."
Checked to make sure spelling is correct
Thanks
AB
-
The query worked for me when I create a test table.
Unless the data is sensitive, can u post the result of the following query in QA (including the headings)
SELECT TOP 1 * FROM [Course Bookings]
Far away is close at hand in the images of elsewhere.
Anon. -
i made the mistake of not putting the display name in on the last bit.
SELECT b.[Client ID],b.[Course Date],b.[Course ID]
FROM [Course Bookings] b
INNER JOIN (SELECT [Client ID],MAX([Course Date]) AS [Course Date 1]
FROM [Course Bookings]
GROUP BY [Client ID]) a
ON b.[Client ID] = a.[Client ID]
AND b.[Course Date] = a.[Course Date 1]
thank you David for All your help!!!!!
-
Cancel Request Figured it out by my self
What if the tables were slightly different
Course Details
Course ID
Course Date
Course Bookings
Course Code
client ID
client details
Client ID
Client Details
would it still be the same ?
Thanks
-
Just for reference, two possibilities
SELECT b.[Client ID],b.[Course ID],d.[Course Date]
FROM [Course Bookings] b
INNER JOIN [Course Details] d
ON d.[Course ID] = b.[Course ID]
WHERE b.[Course ID] IN (SELECT TOP 1 cb.[Course ID]
FROM [Course Bookings] cb
INNER JOIN [Course Details] cd
ON cd.[Course ID] = cb.[Course ID]
WHERE cb.[Client ID] = b.[Client ID]
ORDER BY cd.[Course Date] DESC)
SELECT b.[Client ID],b.[Course ID],d.[Course Date]
FROM [Course Bookings] b
INNER JOIN [Course Details] d
ON d.[Course ID] = b.[Course ID]
INNER JOIN (SELECT cb.[Client ID],MAX(cd.[Course Date]) AS [Course Date]
FROM [Course Bookings] cb
INNER JOIN [Course Details] cd
ON cd.[Course ID] = cb.[Course ID]
GROUP BY cb.[Client ID]) a
ON a.[Client ID] = b.[Client ID]
AND a.[Course Date] = d.[Course Date]
depending on performance
Far away is close at hand in the images of elsewhere.
Anon. -
select clientid, coursedate, max([course code]) coursecode
from [course bookings] b,
( SELECT [client id] clientid, MAX([course date]) coursedate
FROM dbo.[course bookings]
GROUP BY [client id]
) as d
where d.clientid = b.[client id] and d.coursedate = b.[course date]
group by clientid, coursedate
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply