How to use SQL to filter a Column to equal to Two Conditions Simultaneously?

Question

I have a table called PM_BATCH_ST like this:

BATCH_ID	RESULT
1000564932	ISC
1000564932	LLC
1000585739	ISC
1000585739	LLC
1000384769	ISC
1000384769	LLC
1000384757	LLC
1000888940	ISC

Can I filter the Table to obtain BATCH_IDs that have both an ISC entry and an LLC entry? So the Answer I would like is:

1000564932

1000585739

1000384769

Does this answer your question? [SQL query: Simulating an "AND" over several rows instead of sub-querying](https://stackoverflow.com/questions/163887/sql-query-simulating-an-and-over-several-rows-instead-of-sub-querying) — astentx, Jul 18 '23 at 12:46

Qubicon · Answer 1 · 2023-08-30T21:46:25.677

4

SELECT BATCH_ID
FROM PM_BATCH_ST
WHERE RESULT IN ('ISC', 'LLC')
GROUP BY BATCH_ID
HAVING COUNT(DISTINCT RESULT) = 2;

edited Aug 30 '23 at 21:46

answered Jul 18 '23 at 12:42

Qubicon

51
3

score 1 · Answer 2 · answered Jul 18 '23 at 12:43

1

I think the following would do:

select distinct batch_id
from  PM_BATCH_ST a
where a.result = 'ISC' and 
  exists (
    select 1
    from  PM_BATCH_ST b
    where a.batch_id=b.batch_id 
      and b.result='LLC'
  )

answered Jul 18 '23 at 12:43

Bogdan Dincescu

683
6

Rafalon · Answer 3 · 2023-07-20T13:26:09.233

1

For 2 values, I would just self-JOIN and use a WHERE clause like this:

SELECT st1.BATCH_ID
FROM PM_BATCH_ST st1
JOIN PM_BATCH_ST st2 ON st1.BATCH_ID = st2.BATCH_ID
WHERE st1.RESULT = 'ISC' AND st2.RESULT = 'LLC'

That should give you all BATCH_ID having both ISC and LLC as RESULT

SQL Fiddle results in:

BATCH_ID
1000564932
1000585739
1000384769

edited Jul 20 '23 at 13:26

answered Jul 18 '23 at 12:48

Rafalon

4,450
2
16
30

score 1 · Answer 4 · answered Jul 20 '23 at 08:24

1

This can be done using INTERSECT

SELECT BATCH_ID
FROM PM_BATCH_ST
WHERE RESULT = 'ISC'

INTERSECT

SELECT BATCH_ID
FROM PM_BATCH_ST
WHERE RESULT = 'LLC'

Result:

answered Jul 20 '23 at 08:24

Vhon Newmahn

111
2
5

score 0 · Answer 5 · answered Jul 18 '23 at 15:32

CREATE TABLE PM_BATCH_ST (
    BATCH_ID INT,
    RESULT TEXT
);


INSERT INTO PM_BATCH_ST (BATCH_ID, RESULT)
VALUES
    (1000564932, 'ISC'),
    (1000564932, 'LLC'),
    (1000585739, 'ISC'),
    (1000585739, 'LLC'),
    (1000384769, 'ISC'),
    (1000384769, 'LLC'),
    (1000384757, 'LLC'),
    (1000888940, 'ISC');

/* @Qubicon has the most tighten-up answer. Below is another way of doing it*/

WITH ISC_table AS (
  SELECT *
  FROM PM_BATCH_ST
  WHERE CONVERT(VARCHAR, RESULT) IN ('ISC')
),
    LLC_table AS (
  SELECT *
  FROM PM_BATCH_ST
  WHERE CONVERT(VARCHAR, RESULT) IN ('LLC')
)

SELECT
  t1.BATCH_ID AS BATCH_ID
FROM ISC_table AS t1
INNER JOIN LLC_table AS t2
ON t1.BATCH_ID = t2.BATCH_ID;

BATCH_ID
1000564932
1000585739
1000384769

fiddle

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jul 21 '23 at 19:54

How to use SQL to filter a Column to equal to Two Conditions Simultaneously?

5 Answers5