Check if all ID's in a Column have a specific value in another column, different tables

Question

I have two tables:

Table 1:

Id 
1232
1344
1313
4242
3242
555

Table 2:

Id    sym    mnth  code
1232    9      1     32
1344    15     1     14
1313    10     1     32
4242    11     1     32
3242    9      1     32
1232    9      2     32
1344    13     2     14
1313    9      2     32
4242    10     2     32
3242    9      2     32

I want to check if all the id's in table 1 have the value 9 in sym for all the month (1,2 in the example) but only for those id's which for them the code in table 2 is '32'.

If not return me the id and the months for which the 9 is missing separate by a comma. If Id in table 1 doesn't exists at all in table 2 return null in the month column and the id.

The output in the example should be:

ID    month
1313    1
4242    1,2
555    NULL

1344 doesn't exist because the code column for hime is not 32.

I started writing this:

SELECT table1.id
FROM table1
WHERE not EXISTS (SELECT id FROM table2 
                  WHERE table2.sml = '9' AND table2.code = 32)

But I really don't know how to make the query run for all month and plug the results like I've mentioned in the output. Any help?

Thank you!

Answer 1

You have a lot of conditions in your request that don't compliment each other so well, so the query is likely going to look a bit messy, and probably perform slow. You'll need a way to combine results into your comma-delimited list. SQL Server doesn't have a built-in string concatenation aggregate function, so you'll need to work on something similar to this other question in order to get the month output you are after.

What I've come up with that gives you the results you are after is:

SELECT t1.id, t2.[month]
FROM Table1 t1
OUTER APPLY (
  SELECT stuff((SELECT ', ' + convert(varchar, mnth)
                FROM Table2
                WHERE id = t1.id and sym <> 9 and code = 32
                ORDER BY mnth ASC
                for xml path('')
               ),1,2,'') as [month]
) t2
WHERE
    id in (SELECT id FROM Table2 WHERE sym <> 9 and code = 32)
or  id not in (SELECT id FROM Table2);

Note that I added the ORDER BY mnth ASC line so that the result month field has the non-9-sym months in logical order. If you'd rather see the order they appear in the table, just remove this line.

Edit: Removed the initial "thinking out loud" answer and left just the actual solution to prevent confusion.

Answer 2

Try this:

select * from
  (select 
        id,
        stuff((select ', ' + convert(varchar, month_)
               from tbl2 t2 
               where t1.id = t2.id 
               and t2.sym != 9 and t2.code != 32
               for xml path('')),1,2,'') month_
    from tbl1 t1
    group by id 

UNION

select t1.id, convert(varchar, t2.month_)
               from tbl1 t1
                JOIN tbl2 t2 on t1.id = t2.id 
               and t2.sym = 9 and t2.code = 32) as t

where t.month_ is not null

Answer 3

• Here I create a cte to find out what are the month missing.

• Has to create a derivated table months to include all the months.

• Then perform a left join, so either didnt exist one item in table 2 to match or the item was the wrong one

• Once I have all the wrong link create the string using XML PATH

  • Concatenate many rows into a single text string?

Sql Fiddle Demo

WITH cte as (
    SELECT t1.*, t2.sym, t2.mnth, t2.code
    FROM Table1 t1
    CROSS JOIN (select 1 month_id union select 2) months    
    LEFT JOIN Table2 t2
      ON t2.[id] = t1.id
     AND t2.[mnth] = months.month_id
    WHERE  ([code] = 32 OR [code] is NULL)
      AND  ([sym] <> 9 OR [sym] is NULL)

), MAIN as (
    SELECT DISTINCT c2.Id, (SELECT c1.mnth + ',' as [text()]
                            FROM cte c1
                            WHERE c1.Id = c2.Id
                            ORDER BY c1.Id
                            For XML PATH ('')
                ) [months]
    FROM cte c2
)
SELECT Id,
       IIF( len([months]) > 0, 
            LEFT ([months], len([months])-1),
            NULL) as [months]
FROM Main  

OUTPUT

|   Id | months |
|------|--------|
|  555 | (null) |
| 1313 |      1 |
| 4242 |    1,2 |