[Solved] Query criteria using AND on the same field
[Solved] Query criteria using AND on the same field
Hi
I have a query, which I would like to only show a list of all staff who have completed three different training courses. I have attached the relationships. I would like to know who has completed CourseTitle: Asbestos Awareness, Facefit SR100 and Asbestos Medical, but I'm struggling to get a list.
To make it more difficult, the Asbestos medical has an expiry date, so I would only like those who's course is active in the list too.
Is this something that I can even do within Base?
Thanks for any help in advance.
I have a query, which I would like to only show a list of all staff who have completed three different training courses. I have attached the relationships. I would like to know who has completed CourseTitle: Asbestos Awareness, Facefit SR100 and Asbestos Medical, but I'm struggling to get a list.
To make it more difficult, the Asbestos medical has an expiry date, so I would only like those who's course is active in the list too.
Is this something that I can even do within Base?
Thanks for any help in advance.
Last edited by Hagar Delest on Tue Dec 11, 2018 9:21 am, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
OpenOffice 4.1.5 on Windows 7
Re: Query criteria using AND on the same field
What about:
Did not validate the SQL in all details, but this might help to find your way.
Code: Select all
SELECT OL."Surename", CL."CourseTitle"
FROM "CourseList" CL
INNER JOIN "Training Log" TL ON CL."CourseID" = TL."CourseID"
INNER JOIN "Ops List" OL ON TL."OpID" = OL."OpID"
WHERE
CL."CourseTitle" ='Asbestos Awareness'
OR CL."CourseTitle" ='Facefit SR100'
OR CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE
Edit: Literals with single quotes iso of double, thanks to Sliderule |
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: Query criteria using AND on the same field
Thanks, this really helps me. However, it gives the list of everyone who has one, two or three of them. I would like a list of people who only have all three. Is that possible?
OpenOffice 4.1.5 on Windows 7
Re: Query criteria using AND on the same field
Code: Select all
WHERE
CL."CourseTitle" ='Asbestos Awareness'
and CL."CourseTitle" ='Facefit SR100'
and CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE
Edit: Incorrect advice |
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Query criteria using AND on the same field
That gives me no one on the list, but there are people that have all three records under their name?
OpenOffice 4.1.5 on Windows 7
Re: Query criteria using AND on the same field
Try experimenting with your query:
1: Note a guy that has all three
2: Remove both conditions with AND and validate that guy is listed
3: Add one of the two conditions and validate the guy again
4: Repeat step 3 till it is failing. Now examine your database in great detail.
Doing it yourself is the best way of learning.
1: Note a guy that has all three
2: Remove both conditions with AND and validate that guy is listed
3: Add one of the two conditions and validate the guy again
4: Repeat step 3 till it is failing. Now examine your database in great detail.
Doing it yourself is the best way of learning.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: Query criteria using AND on the same field
Code: Select all
WHERE
CL."CourseTitle" ='Asbestos Awareness'
and CL."CourseTitle" ='Facefit SR100'
and (CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE)
Edit: Incorrect advice |
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Query criteria using AND on the same field
The approach of finding users that satisfy all conditions need to be solved differently: might be too difficult to realize based on experimenting.
The WHERE-clause conditions will check for a single records (defined relationship). So the check on CL."CourseTitle" refers to a single record and can never be AND a AND b at the same time.
You have two possible solutions.
- Take the query with the OR on CL."CourseTitle" and count if you find users with three hits.
- Create 3 relations from the CL table each for one specific "CourseTitle".
The latter might look like:
Note: that the conditions are shifted from the WHERE-clause to the specific JOIN condition: this is to express the tables relation
The WHERE-clause conditions will check for a single records (defined relationship). So the check on CL."CourseTitle" refers to a single record and can never be AND a AND b at the same time.
You have two possible solutions.
- Take the query with the OR on CL."CourseTitle" and count if you find users with three hits.
- Create 3 relations from the CL table each for one specific "CourseTitle".
The latter might look like:
Code: Select all
SELECT OL."Surename"
FROM "Ops List" OL
INNER JOIN "Training Log" TL ON TL."OpID" = OL."OpID"
INNER JOIN "CourseList" CL1 ON CL1."CourseID" = TL."CourseID"
AND CL1."CourseTitle" ='Asbestos Awareness'
INNER JOIN "CourseList" CL2 ON CL1."CourseID" = TL."CourseID"
AND CL2."CourseTitle" ='Facefit SR100'
INNER JOIN "CourseList" CL3 ON CL1."CourseID" = TL."CourseID"
AND CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: Query criteria using AND on the same field
What was I thinking? Sorry for the brain hiccup and ignore my two posts aboveSo the check on CL."CourseTitle" refers to a single record and can never be AND a AND b at the same time
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Query criteria using AND on the same field
Thanks I'll play around with these to see if I can get it all to work.......
OpenOffice 4.1.5 on Windows 7
Re: Query criteria using AND on the same field
I'm not having much luck on the relations. It's just freezing OpenOffice each time. I might just export the list of all in the OR list to Excel and create an easy pivot table for the right data.
Unless anyone else has any ideas I can play around with?
Unless anyone else has any ideas I can play around with?
OpenOffice 4.1.5 on Windows 7
Re: Query criteria using AND on the same field
Can you attach a test document (stripped private data) so our proposals can be tested. It might be that the SQL is getting complex. So you might try this as alternative:
Note: each OL.field in the SELECT-clause should also be listed in the GROUP-BY clause. This only works if there are no double entries in the log for any course / user combination.
Code: Select all
SELECT OL."Surename"
FROM "CourseList" CL
INNER JOIN "Training Log" TL ON CL."CourseID" = TL."CourseID"
INNER JOIN "Ops List" OL ON TL."OpID" = OL."OpID"
WHERE
CL."CourseTitle" ='Asbestos Awareness'
OR CL."CourseTitle" ='Facefit SR100'
OR CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE
GROUP BY OL."Surename"
HAVING COUNT(*) = 3
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: Query criteria using AND on the same field
Oh my - that's fixed it!!!! Thank you so much. I'll practice with this so I understand it going forwards. The final SQL code I used is:
There will be duplicate records in the future, but there is a 'RefresherTrainingCompleted' tick box which I can use to exclude the old entries
Thanks again for all your help on this.
Code: Select all
SELECT OL."FullName"
FROM "Course List" CL
INNER JOIN "Training Log" TL ON CL."CourseID" = TL."CourseID"
INNER JOIN "Ops List" OL ON TL."OpID" = OL."OpID"
WHERE
CL."CourseTitle" ='Asbestos Awareness'
OR CL."CourseTitle" ='Facefit SR100' AND TL."TrainingExpiryDate" >= CURRENT_DATE
OR CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingExpiryDate" >= CURRENT_DATE
GROUP BY OL."FullName"
HAVING COUNT(*) = 3
Thanks again for all your help on this.
OpenOffice 4.1.5 on Windows 7