There's generally two ways to approach this depending on your background:
- Think about the scenario in PSQuery and build it in PSQuery
- Think about the scenario in SQL, write and test in SQL and then replicate the logic in PSQuery
I prefer the second approach, as I think it lets you be more expressive and utilise the full power of SQL. Then the challange isn't how do you write the query, it becomes how to do you make PSQuery generate the SQL…
Since I don't have the table/view names, I figure the SQL might look something like this?
select A.ID, A.SUBJECT, A.CRSE_NUMBER, A.SECTION, count(distinct B.ORG)
from TABLE1 A inner join TABLE2 B
on A.SUBJECT = B.SUBJECT
group by A.ID, A.SUBJECT, A.CRSE_NUMBER, A.SECTION
having count(distinct B.ORG) > 1
This joins the two tables together on subject, groups by the fields in TABLE1 and then counts the number distinct ORGs in TABLE2 and only returns cases where there is more than 1 distinct ORG.
To do this PSQuery:
- Add your two tables in the query, standard join on the common fields (e.g. INSTITUTION, SUBJECT)
- Select the appropriate fields you need from TABLE1
- Click on Having, and add the having criteria. You will need to add an expression which is count(distinct B.ACAD_ORG) as an aggregate function so that it generates the correct having SQL. I don't think you can get around this without using an expression. Make it greater than or equal to a constant value of 1.
- Go to expressions and add your expression as a field as well (to list the distinct ORGs)
Here's a screenshot of the expression I think you would need:
Here's a screenshot of the having crteria properties you would then use:
Note this took a bit of fiddling to get right in PSQuery, hopefully I have the steps right, please reply if you have any problems.
mkjj 1259779184|%e %b %Y, %H:%M %Z|agohover
