SQL QUERY that stops the process once match is found.

Database

SQL QUERY that stops the process once match is found.



Hi,
I am stuck up in below situation, where I am not able to proceed further.
Could u please help in resolving the below query
Table A with fields Name and ID
Table B with fields with Name, ID,Code

I want to derive fields
My query is like this

Select
Distinct
A.Name,A.ID
(CASE WHEN B.CODE IN ('X','Y') THEN 'YES' ELSE 'NO' END) AS SVC,
( CASE WHEN B.CODE IN ('U','V') THEN 'YES' ELSE 'NO' END) AS PGM
 from A
Left outer join
B
On A.NAME =B.NAME
AND A.ID = B.Id

Table A
Name ID
Al 12

I have data in B tables in a way, where for same Name and ID , I would have 4 records with CODE  equal to U,V,X,Y
Table B

Name Id code
Al 12 X
Al 12 Y
Al 12 U
Al 12 V

So while retrieving data for the above query, I get 2 records .
Name ID SVC PGM
Al 12 yes no
Al 12 no yes

Since for the same Name and ID, we have both matching and not matching records,i get both YES and NO.
My requirement is, if atleast one record matches in the IN condition, then the process should stop there, it should not go to the else part.
My requirement should give the result set as below.
Name Id SVC PGM
Al 12 yes yes
Could you please help us on query, that gives result as above.

Thanks
Vidya.
3 REPLIES
Senior Apprentice

Re: SQL QUERY that stops the process once match is found.

Hi Vidya,

you can move the calculation into a Derived Table:

SELECT --no more DISTINCT
A.Name,A.ID,
coalesce(b.SVC, 'NO'),
coalesce(b.PGM, 'NO')
FROM A
LEFT OUTER JOIN
(
SELECT Name, ID,
max(CASE WHEN B.CODE IN ('X','Y') THEN 'YES' END) AS SVC,
max(CASE WHEN B.CODE IN ('U','V') THEN 'YES' END) AS PGM
FROM b
group by Name, ID
) AS B
ON A.Name =B.Name
AND A.ID = B.Id
Enthusiast

Re: SQL QUERY that stops the process once match is found.

Hi Vidya,

I think Dieter's query might still give you two records, one with Yes and another with 'No'. 

Query i am giving should return just a single record as you expected. It is almost the same as Dieter's query but just a small modification.

I have tested it for all the three possible scenarios and is returning the expected result.


INSERT INTO TAB_A
VALUES('AL', '12');

INSERT INTO TAB_B
VALUES('AL', '12', 'X');

INSERT INTO TAB_B
VALUES('AL', '12', 'Y');

INSERT INTO TAB_B
VALUES('AL', '12', 'U');

INSERT INTO TAB_B
VALUES('AL', '12', 'V');

INSERT INTO TAB_A
VALUES('BL', '12');

INSERT INTO TAB_B
VALUES('BL', '12', 'X');

INSERT INTO TAB_B
VALUES('BL', '12', 'Y');

INSERT INTO TAB_B
VALUES('BL', '12', 'W');

INSERT INTO TAB_B
VALUES('BL', '12', 'Z');

INSERT INTO TAB_A
VALUES('CL', '12');

INSERT INTO TAB_B
VALUES('CL', '12', 'A');

INSERT INTO TAB_B
VALUES('CL', '12', 'B');

INSERT INTO TAB_B
VALUES('CL', '12', 'W');

INSERT INTO TAB_B
VALUES('CL', '12', 'Z');

SEL
A.COL_A, A.COL_B,
MAX( B.SVM), MAX(B.PGM)

FROM
TAB_A A
LEFT JOIN
(
SEL
col_a, col_b,
CASE WHEN col_c IN ('x','y') THEN 'YES' ELSE 'NO' END AS SVM,
CASE WHEN col_c IN ('U','V') THEN 'YES' ELSE 'NO' END AS PGM
FROM TAB_B
GROUP BY 1,2,3,4
) B
ON A.COL_A = B.COL_A
AND B.COL_B = B.COL_B
GROUP BY 1,2
ORDER BY 1

Thanks,

Narasimha Sarma.

Re: SQL QUERY that stops the process once match is found.

Thanks Dieter. The query's result matches with my expected results.
Thanks Narasimha for framing the query in another way..will try this as well...

Thanks,
Vidya