Access SQL mutliple HAVING in joined table statement

Question

I have an SQL statement that has multiple joins. I would like to us the IN statement for both tables, like so:

SELECT 
    p.id, p.first_name as [First Name], 
    p.last_name as [Last Name]       
FROM  
    tbl_person as p , 
    tbl_person_languages as pl , 
    tbl_person_skills As ccp  
WHERE  
    pl.language_id in (12,14)  AND  
    ccp.skill_id in (55) 
GROUP BY  
    p.id, p.first_name , p.last_name 
HAVING 
     count(pl.language_id) = 2 and count(ccp.skill_id) =1

So, I would like to pull back all, distinct, records where a person has language_ids equal to 12 AND 14 and have skill_id = 1. But, this returns an empty set. I have confirmed that the data exists for at least one record, so I should see something.

I should note, there will be more joins and the language_ids and skill_ids values can change. This is similar to a previous question I asked (Access SQL using IN where record must satisfy all values) but this is with multiple joined tables. thanks

EDIT

I have updated the SQL to use Joins, as such:

SELECT 
   p.id,
   p.first_name as [First Name], 
   p.last_name as [Last Name]    
FROM    
   (( tbl_person as p      
 INNER JOIN tbl_person_languages as pl 
    ON p.id = pl.person_id)
 INNER JOIN tbl_person_crossCuttingSkills As ccp 
    ON p.id = ccp.person_id)    
WHERE  
   pl.language_id in (12,14) AND 
    ccp.skill_id in (55)  
GROUP BY  
   p.id,
   p.first_name, 
   p.last_name    
HAVING 
    count(pl.language_id) = 2 AND 
    count(ccp.skill_id) =1

But this now creates a syntax error (missing operator).

My guess is that it's empty because of your `HAVING` clause failing due to the lack of joins. I think it's going to count all pl.language_id in your table, and only return language_ids when there are only two people that know that language. Furthermore, since there are no joins, your getting duplicates/miscounts. You need to make sure you join your tables before we can get any further. Also, I suggest using ANSI sql with your joins using the `ON` keyword instead of `WHERE` if for only readability reasons. http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause — MoondogsMaDawg, Dec 05 '16 at 20:17

JL82559 · Answer 1 · 2016-12-05T19:59:34.377

1

Is attribute a string type or numeric type? Use IN operator with quotes like IN ('12, '14') or class attribute to CInt() within IN operator like CInt(language_id) IN (12,14).

SELECT 
    p.id, 
    p.first_name as [First Name], 
    p.last_name as [Last Name]       
FROM  
    tbl_person as p , 
    tbl_person_skills As ccp  
WHERE  
    pl.language_id in ('12','14') 
    AND ccp.skill_id in ('55') 
GROUP BY  
   p.id, 
   p.first_name , 
   p.last_name 
HAVING 
   count(pl.language_id) = 2 
   AND count(ccp.skill_id) =1

edited Dec 05 '16 at 19:59

answered Dec 05 '16 at 19:45

JL82559

43
9

These values are numbers, not strings. They are the foreign keys from another table. And I do not believe ACCESS supports NUMERIC function – jason Dec 05 '16 at 19:52
[chat] Try using CInt() – JL82559 Dec 05 '16 at 19:56

EllieK · Answer 2 · 2016-12-05T21:33:00.603

1

It fails because you have a Cartesian product (join) expressed in your HAVING statement. Try including pl.language_id in the SELECT portion of your query and you should see what I mean.

As suggested by others, you need to create your JOIN statements.

FROM  
tbl_person as p  
INNER JOIN tbl_person_languages as pl 
    ON p.id = pl.person_id_FK
INNER JOIN tbl_person_skills As ccp 
    ON p.id = ccp.person_id_FK

edited Dec 05 '16 at 21:33

answered Dec 05 '16 at 21:25

EllieK

259
4
14

I have updated my original question to post new SQL. It uses the Inner Joins as you recomend but now I get a syntax error (Syntax Error (missing operator) in query expression 'p.id = pl.person_id INNER JOIN tbl_person_skills as ccp ON p.id =person.id' – jason Dec 06 '16 at 17:26
Is it the bracket in the GroupBy? GROUP BY p.id, p.first_nam], And I'm assuming you do have valid foreign keys in the person_id fields in the Skills and Languages tables? – EllieK Dec 06 '16 at 17:33
Sorry, typo in pasting. I will fix in post. Yes, each table has a FK to the tbl_person table – jason Dec 06 '16 at 17:44
You also have pl.ID and ccp.ID in your select but haven't wrapped them in an aggregate function. They need either to be grouped or to be aggregated. – EllieK Dec 06 '16 at 17:46
Ugh, I hate this problem. Access wants parentheses around the join statements. See [this](http://stackoverflow.com/questions/7854969/sql-multiple-join-statement) question. Try: `FROM (tbl_person as p INNER JOIN tbl_person_languages as pl ON p.id = pl.person_id_FK) INNER JOIN tbl_person_skills As ccp ON p.id = ccp.person_id_FK` – MoondogsMaDawg Dec 06 '16 at 18:27
Yeah, I found the same thing. But now, the response comes back empty. I will update my SQL to show the changes – jason Dec 06 '16 at 19:07
Odd behavior, though. if I change my HAVING statement, like this: count(pl.language_id) = 2 AND count(ccp.skill_id) =2 It seems to work. But, I thought the number was suppose to mean the number of parameters in the IN statement? – jason Dec 06 '16 at 19:56
HAVING is applied after the query has returned all of its data. Move the COUNTS from your HAVING clause to the SELECT statement. If you want to act on COUNT values you need to have them counted before the examining their values in the HAVING clause. Then reference the fields from the SELECT clause in your HAVING clause.. – EllieK Dec 07 '16 at 17:15
...And, in addition to my comment above, you do want your counts set to count(pl.language_id) = 2 AND count(ccp.skill_id) =2. If a person speaks two languages, two records one with 12 and one with 14 as langID will return. Both of those records, however, will have 55 as skill ID. So you counts will be 2 and 2. – EllieK Dec 07 '16 at 18:30

Access SQL mutliple HAVING in joined table statement

2 Answers2