[Solved] Query criteria using AND on the same field

[Hagar Delest]

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.

[eremmel]

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

[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?

[robleyd]

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

[AmyBUK]

That gives me no one on the list, but there are people that have all three records under their name?

[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.

[robleyd]

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

[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   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

[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

[AmyBUK]

Thanks I'll play around with these to see if I can get it all to work.......

[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?

[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   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.

[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   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

Thanks again for all your help on this.