ontopofmalvern
Registered User.
- Local time
- Today, 04:35
- Joined
- Mar 24, 2017
- Messages
- 64
Hi, hope this is clear.
I am trying to write a query for a school time table database, this is a simplified version of what I am attempting which hopefully gets to the problem.
table data is -
query is-
SELECT d1.staff, d1.subject, d2.subject
FROM
(
SELECT First(tbl_lessons.subject) As subject, tbl_lessons.staffID AS staff
FROM tbl_lessons
WHERE tbl_lessonss.dayID=1 AND tbl_lessons.unit=1 GROUP BY tbl_lessons.staffID
) AS d1,
(
SELECT First(tbl_lessons.subject) AS subject
FROM tbl_lessons
WHERE tbl_lessons.dayID=1 AND tbl_lessons.unit=2
) AS d2
output is
What I want is next to staff 4 in d2 column ‘Art & Craft M2’ which is the lesson for staff ‘4’ at that time of day. What is returned is staff 3’s lesson, I think I need another ‘AND’ in WHERE clause which makes some reference to ‘staff’, or chane GROUP BY clauses but can’t see how to do it.
Many thanks to all the fabulous people that help us newbies.
I am trying to write a query for a school time table database, this is a simplified version of what I am attempting which hopefully gets to the problem.
table data is -
tbl_lessons | ||||
lessonID | subject | dayID | unit | staffID |
47 | Current affairs M1 | 1 | 1 | 3 |
48 | Current affairs M2 | 1 | 2 | 3 |
77 | Art & Craft M1 | 1 | 1 | 4 |
78 | Art & Craft M2 | 1 | 2 | 4 |
query is-
SELECT d1.staff, d1.subject, d2.subject
FROM
(
SELECT First(tbl_lessons.subject) As subject, tbl_lessons.staffID AS staff
FROM tbl_lessons
WHERE tbl_lessonss.dayID=1 AND tbl_lessons.unit=1 GROUP BY tbl_lessons.staffID
) AS d1,
(
SELECT First(tbl_lessons.subject) AS subject
FROM tbl_lessons
WHERE tbl_lessons.dayID=1 AND tbl_lessons.unit=2
) AS d2
output is
Query1 | ||
staff | d1.subject | d2.subject |
3 | Current affairs M1 | Current affairs M2 |
4 | Art & Craft M1 | Current affairs M2 |
What I want is next to staff 4 in d2 column ‘Art & Craft M2’ which is the lesson for staff ‘4’ at that time of day. What is returned is staff 3’s lesson, I think I need another ‘AND’ in WHERE clause which makes some reference to ‘staff’, or chane GROUP BY clauses but can’t see how to do it.
Many thanks to all the fabulous people that help us newbies.
