Finding Specific Rows in SQL which must Includes Value From ColumnB-CodePudding

I have a table similar to below table. I want to select all ColumnA Values which has the Value "X" for ColumnB but also other possible ColumnB values.

ColumnA	ColumnB
One	X
One	Y
Two	Y
Two	Z
Three	X
Three	Z

So basically the query should result like this. Can you help me to achieve this?

ColumnA	ColumnB
One	X
One	Y
Three	X
Three	Z

CodePudding user response：

One solution: once you know what Table1 values you want you can select them. So resolve that in a subquery. We'll alias the original table as X and the subquery as Y:

select X.* 
from Table1 X
inner join
(
    select 
        ColumnA
    from 
        Table1 
    Where 
        ColumnB = 'X'
) Y
on X.ColumnA = Y.ColumnA

CodePudding user response：

you could use min() over a window:

select columnA, columnB 
from (
    select *, min(columnb) over(partition by columna) mb
    from t
)t
where mb = 'X';

CodePudding user response：

I believe a subquery would be the simplest way. Step 1 - find all the columnA values where columnB = 'x'

SELECT DISTINCT ColumnA
FROM table_name
WHERE ColumnB = 'x'

step 2 - select all the records where the value in ColumnA is in this list

SELECT *
FROM table_name
WHERE ColumnA in (SELECT DISTINCT ColumnA
                  FROM table_name
                  WHERE ColumnB = 'x')