Columns with different WHERE clauses.

[ontopofmalvern]

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 -

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.

 

[arnelgp]

maybe google crosstab query.

 

[ontopofmalvern]

Many thanks, I am slightly embarrassed that I din't think of that, trying to be too clever.