• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

Unsure what SQL is needed for looking up cross tables.

Afternoon all,

Two tables:

Students:
----------------------------
ID Name
0 Student1
1 Student2
2 Student3

StudentActive
----------------------------
StudentID IsActive
0 True
1 True
2 True
1 False

The studentactive table actually logs a history of student activity so there can be more than one student ID as shown with studentID 1.

How can I get a list of students who are active? In the above example, student1 and student3 are active but student 2 isn't. I'm doing this in VB but using Access 2007 as the database backend, but that shouldn't make much of a difference I wouldn't have thought.

Thanks,
Uni
0
Unimatrix_001
Asked:
Unimatrix_001
  • 2
  • 2
1 Solution
 
DigitalSnorkleCommented:
Does StudentActive have some kind of timestamp column?

You can't rely on elements remaining in input order.
0
 
Unimatrix_001Author Commented:
There are a couple more columns in there so yes, but I haven't included them as they aren't too relevant.
0
 
jfmadorCommented:
This query will give you the status for your student for the last timestamp

SELECT s.ID, s.Active FROM Student s
INNER JOIN (
SELECT ID, Max(TimeStamp) as TimeStamp FROM students) t on s.ID = t.ID and s.TimeStamp = t.TimeStamp


0
 
jfmadorCommented:
Sorry I miswritten several field

try this

SELECT Students.StudentID, Students.Name, s.Active
FROM (StudentActive s INNER JOIN (
SELECT StudentID, Max(TimeStamp) as TimeStamp FROM studentActive group by StudentID) t on s.StudentID = t.StudentID and s.TimeStamp = t.TimeStamp) INNER JOIN Students on s.StudentID = Students.ID
0
 
Unimatrix_001Author Commented:
Thank you, :-)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now