Solved

Unsure what SQL is needed for looking up cross tables.

Posted on 2008-10-28
5
193 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql joining from the same table 6 44
SQL NULL vs Blank 26 36
SQL question - need unique values for one column that is not displayed 2 22
convert null in sql server 12 34
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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