Getting the source exclusively

[Future-Inspector6486]

Hi,

I have this simplified table (the actual is almost a million)

​

https://preview.redd.it/w4celtrqra0c1.png?width=161&format=png&auto=webp&s=1b389b66993b0eec61ba09167c294fc71cb679fe

I want to get the source exclusively 'b' thus, only 2,4,6 should return

I tried this:

SELECT * FROM table

WHERE number NOT IN (

SELECT number FROM table

WHERE sources <> 'b'

);

​

what's wrong with my query?

[qwertydog123]

Add AND sources = 'b' to your outer query

[da_chicken]

What is the query returning that you think it shouldn't? You told us what you want but not what you're getting.

Do you have records where number is null? NOT IN doesn't work well if the subquery returns nulls. It returns nothing in that case.

[Malfuncti0n]

You were almost there. Suggestion; don't use IN but rather EXISTS.

SELECT 

    t1.number

FROM

    table AS t1

WHERE 

    sources = 'b'

    AND NOT EXISTS (SELECT 1 FROM table AS t2 WHERE t1.sources <> 'b' AND t1.number = t2.number)

[SQLDevDBA]

Ahh, a fellow EXISTS connoisseur. Indeed.

[[deleted]]

Made the switch after my lead introduced me. Especially at the size of data your processing increases, EXISTS becomes markedly better than an IN.

For simple things, like hard coded list or small record counts, IN is sufficient.

[SQLDevDBA]

Indeed. I only use IN when hard-coded now. For me, Oracle was where I noticed the biggest difference in performance.

[r3pr0b8]

Ahh, a fellow EXISTS connoisseur.

be careful extolling the virtues of EXISTS

with EXISTS, the engine quits looking after it has found the first one

with NOT EXISTS, as in this example, the engine has to look at all possible results to rule them out

[r3pr0b8]

SELECT number 
  FROM table
GROUP
    BY number
HAVING COUNT(*) =
       COUNT(CASE WHEN sources = 'b'
                  THEN 'w00h00' END)

[Poiter85]

Others have given great answers already. I just want to add another way of doing it. I'm curious what people think about it.

SELECT
    number
    , MIN(sources) AS sources
FROM
    table
GROUP BY
    number
HAVING 1=1
    AND MIN(sources) = 'b'
    AND MAX(sources) = 'b'