0

So, here is my SELECT query... it's working, but i want to get (with left join) only message types, which are NOT exists in CONFIG table:

SELECT      PARTNER.ID                          AS [ID],
            PARTNER.NAME                        AS [name],
            PARTNER.GLN                         AS [GLN],
            IIF(CONFIG.DIRECTION=0,'IN','OUT')  AS [Direction],
            COUNT(CONFIG.ID)                    AS [count config]
,STUFF((SELECT ', ' + message_type.message_type
        FROM message_type
        WHERE message_type.id not in (0,7,8,11,12,14,15)
        ORDER BY message_type.id
        FOR XML PATH (''), TYPE
    ).value('.', 'varchar(max)')
, 1, 1, '')

              
FROM        PARTNER LEFT JOIN CONFIG ON PARTNER.ID=CONFIG.PARTNER_ID_EXT 


WHERE       PARTNER.ID  IS NOT NULL AND PARTNER.CHARSET IS NOT NULL
AND PARTNER.NAME NOT LIKE 'TEST%'

GROUP BY    PARTNER.ID, PARTNER.NAME, PARTNER.GLN, CONFIG.DIRECTION
HAVING      COUNT(CONFIG.ID) not in (5,11,16,20)
ORDER BY    [name] asc, [Direction] asc

output

UPDATED

My sample DATA:

sample data

I want to display the message types, which are MISSING from CONFIG.

Expected result:

enter image description here

Improve this question
6
  • Correlate your subquery by adding message_type.{Your Column} = CONFIG.{Your Column} to your WHERE? Commented Jul 21, 2022 at 9:16
  • @Larnu yes, but then i have to add "config.message_type_id" to my "GROUP BY".. and then i will have multiple rows. Commented Jul 21, 2022 at 9:31
  • 3
    Then maybe that's the wrong column; we don't know we have no sample data, and no expected results. You need to correlate the query, and that's the best we can suggest without that. Commented Jul 21, 2022 at 9:32
  • 1
    The earliest SQL Server version still in mainstream support is SQL Server 2017, which has the STRING_AGG aggregate method Commented Jul 21, 2022 at 9:41
  • Sample data and expected results (as text not images) would help immensely Commented Jul 21, 2022 at 9:59
Related questions
2
How to aggregate either unique string value or NULL
80
Get unique values using STRING_AGG in SQL Server
85
Produce DISTINCT values in STRING_AGG
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.