[Solved] Query criteria using AND on the same field

Creating tables and queries
Post Reply
AmyBUK
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

[Solved] Query criteria using AND on the same field

Post 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.
Attachments
Relationships
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
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Query criteria using AND on the same field

Post 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 
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
AmyBUK
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Post 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?
OpenOffice 4.1.5 on Windows 7
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Query criteria using AND on the same field

Post 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]
 Edit: Incorrect advice 
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
AmyBUK
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Post by AmyBUK »

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
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Query criteria using AND on the same field

Post 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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Query criteria using AND on the same field

Post 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]
 Edit: Incorrect advice 
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Query criteria using AND on the same field

Post 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
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Query criteria using AND on the same field

Post 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? :knock: Sorry for the brain hiccup and ignore my two posts above :ouch:
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
AmyBUK
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Post by AmyBUK »

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

Post 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?
OpenOffice 4.1.5 on Windows 7
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Query criteria using AND on the same field

Post 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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
AmyBUK
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Post 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 :D

Thanks again for all your help on this.
OpenOffice 4.1.5 on Windows 7
Post Reply