Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
4
Erster Beitrag:
vor 1 Jahr, 4 Monaten
Letzter Beitrag:
vor 1 Jahr, 4 Monaten
Beteiligte Autoren:
Joel, Paulo Oliveira, cardcoder

[WB19] need help with SQL query

Startbeitrag von Joel am 30.03.2016 15:50

Hi everyone - I need to create a query, but not sure how to do it - I figure I nee several queries and them UNION them together. First I need a select all records in the database that have enrollments - that's easy. The next step is to look at the Attendance database and only keep the records from the first query that have some records in the attendance database. This way if there's no attendance records yet, I don't need to worry about absentees. Finally, out of the records that are left, I need to figure out who hasn't had an attendance record in the last "X" days. So someone that hasn't checked in in the last 10 days or so would be left in the list.

I probably can figure most of this out, but I really have no idea how to exclude records that have no attendance records at all.

Appreciate any help!

Antworten:

Joel,


I assume you are using Mysql. If so, make an sql query -

xdate is user defined - your X days

select * from enrollments, Attendance where
enrollments.ID = Attendance.ID AND Attendance.lastattended < xdate

This should give you everybody on the enrollment list that has not attended since xdate .

Hope this helps,

Mike

von cardcoder - am 30.03.2016 16:16
whitout knowing your db tables structure and database type (HF/CS, MYSQL,...) is dificult but you don't need several queries for this.

In your case you need at least one inner join between enrollments and Attendance. This is just one sample i don't know the structure of your Attendance table it can be a litle more dificult than this depending on your data structure.

select * from enrollments
inner join Attendance ON enrollments.ID = Attendance.ID
where Attendance.lastattended < xdate


check this help page about joins:
http://doc.windev.com/en-US/?2034007

von Paulo Oliveira - am 30.03.2016 16:31
Thanks everyone! Appreciate the input!

von Joel - am 03.04.2016 06:38
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.