[Solved] Query criteria using AND on the same field

Creating tables and queries

[Solved] Query criteria using AND on the same field

Postby AmyBUK » Tue Nov 13, 2018 2:16 pm

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.
Attachments
Training Relationships.PNG
Relationships
Last edited by Hagar Delest on Tue Dec 11, 2018 9:21 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.5 on Windows 7
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Postby eremmel » Tue Nov 13, 2018 10:47 pm

What about:
Code: Select all   Expand viewCollapse view
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 
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: Query criteria using AND on the same field

Postby AmyBUK » Wed Nov 14, 2018 10:57 am

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
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Postby robleyd » Wed Nov 14, 2018 11:03 am

Replace the OR with AND

Code: Select all   Expand viewCollapse view
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
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3025
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Query criteria using AND on the same field

Postby AmyBUK » Wed Nov 14, 2018 11:09 am

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
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Postby eremmel » Wed Nov 14, 2018 11:48 am

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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: Query criteria using AND on the same field

Postby robleyd » Wed Nov 14, 2018 1:47 pm

Might need parentheses on the last two criteria
Code: Select all   Expand viewCollapse view
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
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3025
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Query criteria using AND on the same field

Postby eremmel » Wed Nov 14, 2018 2:01 pm

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   Expand viewCollapse view
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
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: Query criteria using AND on the same field

Postby robleyd » Wed Nov 14, 2018 2:06 pm

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? :knock: Sorry for the brain hiccup and ignore my two posts above :ouch:
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3025
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Query criteria using AND on the same field

Postby AmyBUK » Wed Nov 14, 2018 2:18 pm

Thanks :D I'll play around with these to see if I can get it all to work.......
OpenOffice 4.1.5 on Windows 7
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Postby AmyBUK » Wed Nov 14, 2018 3:03 pm

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?
OpenOffice 4.1.5 on Windows 7
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Postby eremmel » Wed Nov 14, 2018 4:09 pm

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   Expand viewCollapse view
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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: Query criteria using AND on the same field

Postby AmyBUK » Wed Nov 14, 2018 4:24 pm

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   Expand viewCollapse view
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 :D

Thanks again for all your help on this.
OpenOffice 4.1.5 on Windows 7
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests