CODEWITHSUNDEEP
sql
Jimmy Live
Jimmy Live

Reputation: 271

Searching in SQL based on values in 2 columns

I currently have 2 columns for my database and I'm trying to return all values in column 1 that don't contain a certain value in column two:

ex: Column 1 has 9 digit random value, sometimes repeated. There are 4 different options for column 2; P1, P2, P3, P4.

I'm trying to only display values in column 1 that don't have a value of P4 in column 2. If they don't have a P4, then I want them all to be displayed, but once a Column 1 value is associated with P4, I don't want any of the column 1 values displayed. This process will continue through all column 1 values until the only values displayed in column 1 are values that do not have a P4 column 2 value associated with them.

Upvotes: 0

Views: 76

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

This is an example of a query where you are looking at sets within sets -- that is, sets of column2 within values of column1. I prefer using group by and having for these queries:

select column1
from t
group by column1
having sum(case when column2 = 'P4' then 1 else 0 end) = 0

To get all the values, you would join back to the original table:

select t.*
from t join
     (select column1
      from t
      group by column1
      having sum(case when column2 = 'P4' then 1 else 0 end) = 0
     ) c1
     on t.column1 = c1.column1

Upvotes: 0

Brandon
Brandon

Reputation: 10028

Wouldn't this just be

SELECT column1 FROM <table> WHERE column2 != 'P4'

Upvotes: 0

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

You mean something like this?

SELECT *
FROM YOUR_TABLE
WHERE COLUMN1 NOT IN (
    SELECT COLUMN1
    FROM YOUR_TABLE
    WHERE COLUMN2 = 'P4'
)

Upvotes: 2

Related Questions