Solved

Unsure what SQL is needed for looking up cross tables.

Posted on 2008-10-28
5
194 Views
Last Modified: 2012-05-05
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
Comment
Question by:Unimatrix_001
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:DigitalSnorkle
ID: 22823799
Does StudentActive have some kind of timestamp column?

You can't rely on elements remaining in input order.
0
 
LVL 3

Author Comment

by:Unimatrix_001
ID: 22823951
There are a couple more columns in there so yes, but I haven't included them as they aren't too relevant.
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22825554
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
 
LVL 5

Accepted Solution

by:
jfmador earned 500 total points
ID: 22825584
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
 
LVL 3

Author Closing Comment

by:Unimatrix_001
ID: 31510821
Thank you, :-)
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Deleting Rows from an Oracle Database - Performance 19 53
Email Header Detail 12 63
Get number of Files in Directory and Sub Directories 2 48
SubQuery link 4 35
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question