0

I have a table which has a column name lets say message Id and the message Id may have lot of keyname and value stored in separate column .

MessageID Keyname Value
First ab 1
Second ab 1
Third ac 2
First kk 4

So I need to get the message Id which has "ab" keyname but not "kk". The answer am expecting is Second as it dont have "kk" Keyname.

How could we achieve it ?

lemon
  • 14,875
  • 6
  • 18
  • 38
Senthil
  • 79
  • 1
  • 11
  • not sure i understand the question, if you are filtering out 'ab' and 'kk' aren't you looking for Third? – zhiguang May 29 '23 at 15:30
  • select the messageId that have keyname = 'ab' and in the resulting message Id look for messageId which dont contain keyname = 'kk' – Senthil May 29 '23 at 15:39
  • "*Filter out 'ab' keyname*" means not expecting it in the result set, probably you meant "filter in" according to your expected output. – lemon May 29 '23 at 15:43

4 Answers4

0

You can use conditional aggregation to gather MessageIDs that satisfy your conditions:

SELECT MessageID
FROM tab
GROUP BY MessageID
HAVING COUNT(CASE WHEN Keyname = 'ab' THEN 1 END) > 0
   AND COUNT(CASE WHEN Keyname = 'kk' THEN 1 END) = 0
lemon
  • 14,875
  • 6
  • 18
  • 38
  • 1
    Else 0 is required here – SelVazi May 29 '23 at 16:09
  • You're right. Probably with count, the else clause can be avoided altogether. – lemon May 29 '23 at 16:12
  • Using count is also incorrect have a look on comments in my answer here https://stackoverflow.com/questions/76351125/filtering-by-count-and-specific-value-from-a-related-table/76352267#76352267 – SelVazi May 29 '23 at 16:32
  • 1
    That's a different issue. That happens when you don't use the `CASE` expression inside the count. He's also telling you to use the CASE expression explicitly: `"*and non-compliant DBMS usually require COUNT(CASE WHEN locale='en' THEN 1 END)*", in DBMSs different that MySQL, like Snowflake. – lemon May 29 '23 at 16:36
  • 1
    To deepen it, the `CASE` expression is generating a field that has either 1 or NULL (thanks to the missing `ELSE` clause), which allows the `COUNT` to operate correctly. When you don't use that `CASE` expression, MySQL will return either 1 or 0, and `COUNT` will always count you all non-null values, hence every value returned by `field='value'`. – lemon May 29 '23 at 16:40
  • My mistake, you are correct :) – SelVazi May 29 '23 at 16:48
0

You can us join condition in where clause

SELECT MessageID
FROM tab
WHERE keyname = 'ab' or  keyname != 'kk'
Jitendra Nandre
  • 121
  • 7
0

you can simply use a subquery

select * from (select * from test where keyname = 'ab')
as tab where keyname != 'kk';

fiddle here

zhiguang
  • 345
  • 1
  • 7
  • No this dint work reason it the first subquery return all the rows matching to keyname= 'ab' but when you apply keyname != 'kk' , it just gives the same rows first and second again. Where as my expection is only to get the value as "Second" – Senthil May 29 '23 at 15:50
  • in your data First and Second are duplicate entries, why do you expect one over another? – zhiguang May 29 '23 at 15:52
  • The message Id can have multiple keyname and values. SO in my case MessageID first has keyname "ab" and "kk" where as my message id second has only "ab". I need to get the message Id which has "ab" keyname but not "kk" – Senthil May 29 '23 at 15:54
0

This is an other way to do it using the conditional aggregation by function sum() :

SELECT MessageID
FROM mytable
GROUP BY MessageID
Having
sum(case when Keyname = 'ab' then 1 else 0 end)> 0 and
sum(case when Keyname = 'kk' then 1 else 0 end) = 0
SelVazi
  • 10,028
  • 2
  • 13
  • 29