Translating Access SQL queries into MySQL subqueries.

[timn]

I have a query in Access that uses a subquery, I would like some help on translating it into SQL as a subquery so I can use it in an application.

the queries in Access are
the subquery... (query4)
SELECT Movement.DateTime, Movement.StudentID, Movement.Subject, Movement.Teacher, Movement.Destination, Movement.Lesson
FROM Movement
WHERE (((Movement.DateTime) Between #6/15/2007 7:0:0# And #6/15/2007 19:0:0#) AND ((Movement.Destination)="Away" Or (Movement.Destination)="sign out" Or (Movement.Destination)="sign in") AND ((Movement.Lesson)=3)) OR (((Movement.DateTime) Between #6/15/2007 7:0:0# And #6/15/2007 19:0:0#) AND ((Movement.Destination)="sign in"));


the main query...
SELECT NStudTT.Student, NStudTT.Day, NStudTT.Lesson, NStudTT.Subject, Query4.DateTime, Query4.Subject, Query4.Teacher, Query4.Destination, Query4.Lesson, NStudTT.StudentID
FROM Query4 RIGHT JOIN NStudTT ON Query4.StudentID=NStudTT.StudentID
WHERE (((NStudTT.Day)=5) AND ((NStudTT.Lesson)=3) AND ((NStudTT.Subject) Like "stu*"));


thank you

[Atli]

Hi.

First of all you will have to replace all double quote marks ("..") with single quote marks ('..'), MySQL does not use double quote marks.

You will also have to replace the values you use in you BETWEEN statements. Take a look at the Date Functions in the MySQL Refrence Manual, specificly the Maketime function

[Atli]

I've edited the thread's title to better describe it's contents.
Please read the Posting Guidlines before posting

Moderator

[timn]

Ta

How do you combine the two queries into one SQL statement in SQL?

Cheers

Hi.

First of all you will have to replace all double quote marks ("..") with single quote marks ('..'), MySQL does not use double quote marks.

You will also have to replace the values you use in you BETWEEN statements. Take a look at the Date Functions in the MySQL Refrence Manual, specificly the Maketime function