3

I have two tables.

I am a total newbie to SQL. Using mysql at the moment. I have the following setup for a school-related db:

Table A contains students records. Student's id, password,name, lastname and so on.

Table B contains class attendancy records. Each record goes like this: date, student id, grade

I need to gather all the student info of students that attended classes in a certain date range.

Now, the stupid way would be

  1. first I SELECT all classes from Table B with DATE IN BETWEEN the range

  2. then for each class, I get the student id and SELECT * FROM students WHERE id = student id

What I can't wrap my mind around is the smart way. How to do this in one query only. I am failing at understanding the concepts of JOIN, UNION and so on...

my best guess so far is

SELECT students.id, students.name, students.lastname 
FROM students, classes
WHERE classes.date BETWEEN 20140101 AND 20150101 
AND 
classes.studentid = students.id

but is this the appropriate way for this case?

adam_b
  • 33
  • 1
  • 4
  • Have you got the solution? adam_b – Hardik Parmar Nov 11 '14 at 02:49
  • See this [link](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins/38578#38578). Also, `WHERE classes.date BETWEEN AND ` (no `IN`) – programmer43229 Nov 11 '14 at 02:52
  • Other than the syntax error mentioned above. What is the issue with the query? by joining the 2 tables you are just querying once. – TheProvost Nov 11 '14 at 03:10
  • TheProvost, the query works, I just thought there was a better way involving join or union.. which I currently don't understand yet – adam_b Nov 11 '14 at 03:37

2 Answers2

0

Dont add the join statement in the where clause. Do it like this:

SELECT s.id, s.name, s.lastname,c.date,c.grade 
FROM classes c
inner join students s
on c.studentid=s.id
WHERE c.date BETWEEN '01/01/2014' AND '01/01/2015'
TheProvost
  • 1,832
  • 2
  • 16
  • 41
  • thanks, I think this is what I was looking for, as it improves on my query while implicitly explaning how JOIN works – adam_b Nov 11 '14 at 03:41
0

This sounds like an assignment so I will attempt to describe the problem and give a hint to the solution.

An example of a union would be;

SELECT students.name, students.lastname
FROM students
WHERE students.lastname IS NOT NULL

UNION

SELECT students.name, 'N/A'
FROM students
WHERE students.lastname IS NULL;

+--------------+--------------+
| name         | lastname     |
+--------------+--------------+
| John         | Doe          |   <- First two rows came from first query
| Jill         | Smith        |
| Bill         | N/A          |   <- This came from the second query
+--------------+--------------+

The usual use case for a union is to display the same columns, but munge the data in a different way - otherwise you can usually achieve similar results through a WHERE clause.

An example of a join would be;

SELECT authors.id, authors.name, books.title
FROM authors LEFT JOIN books ON authors.id = books.authors_id

+--------------+--------------+------------------+
| id           | name         | title            |
+--------------+--------------+------------------+   
| 1            | Mark Twain   | Huckleberry Fin. |
| 2            | Terry Prat.. | Good Omens       |
+--------------+--------------+------------------+   
    ^ First two columns from     ^ Third column appended
      from authors table           from books table linked
                                   by "author id"

Think of a join as appending columns to your results, a union is appending rows with the same columns.

In your situation we can rule out a union as you don't want to append more student rows, you want class and student information side by side.

harvey
  • 2,945
  • 9
  • 10