Page 1 of 1
[Solved] Query criteria using AND on the same field
Posted: Tue Nov 13, 2018 2:16 pm
by AmyBUK
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.
Re: Query criteria using AND on the same field
Posted: Tue Nov 13, 2018 10:47 pm
by eremmel
What about:
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
Did not validate the SQL in all details, but this might help to find your way.
| Edit: Literals with single quotes iso of double, thanks to Sliderule |
Re: Query criteria using AND on the same field
Posted: Wed Nov 14, 2018 10:57 am
by AmyBUK
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?
Re: Query criteria using AND on the same field
Posted: Wed Nov 14, 2018 11:03 am
by robleyd
Replace the OR with AND
Code: Select all
WHERE
CL."CourseTitle" ='Asbestos Awareness'
and CL."CourseTitle" ='Facefit SR100'
and CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE
[/strike]
Re: Query criteria using AND on the same field
Posted: Wed Nov 14, 2018 11:09 am
by AmyBUK
That gives me no one on the list, but there are people that have all three records under their name?
Re: Query criteria using AND on the same field
Posted: Wed Nov 14, 2018 11:48 am
by eremmel
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.
Re: Query criteria using AND on the same field
Posted: Wed Nov 14, 2018 1:47 pm
by robleyd
Might need parentheses on the last two criteria
Code: Select all
WHERE
CL."CourseTitle" ='Asbestos Awareness'
and CL."CourseTitle" ='Facefit SR100'
and (CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE)
[/strike]
Re: Query criteria using AND on the same field
Posted: Wed Nov 14, 2018 2:01 pm
by eremmel
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:
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
Note: that the conditions are shifted from the WHERE-clause to the specific JOIN condition: this is to express the tables relation
Re: Query criteria using AND on the same field
Posted: Wed Nov 14, 2018 2:06 pm
by robleyd
So the check on CL."CourseTitle" refers to a single record and can never be AND a AND b at the same time
What was I thinking?
Sorry for the brain hiccup and ignore my two posts above
Re: Query criteria using AND on the same field
Posted: Wed Nov 14, 2018 2:18 pm
by AmyBUK
Thanks
I'll play around with these to see if I can get it all to work.......
Re: Query criteria using AND on the same field
Posted: Wed Nov 14, 2018 3:03 pm
by AmyBUK
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?
Re: Query criteria using AND on the same field
Posted: Wed Nov 14, 2018 4:09 pm
by eremmel
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:
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
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.
Re: Query criteria using AND on the same field
Posted: Wed Nov 14, 2018 4:24 pm
by AmyBUK
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:
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
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.