September 28, 2015 at 11:11 am
Hi,
I thought it will be easy for me but realize that kinda tricky + I care about performance as I have a lot of data for this select. I'd like to get only those records which has
PLAN_ID 1200 AND 8888 ( or ALPHA and ZULU).
I did something like below, but think this could be done without that extra self join ? I care about performance.
Thanks much for your help
Mario
WITH CTE AS (
SELECT '100' PAT_ID, 1200 PLAN_ID, 'ALPHA' PLAN_NAME UNION
SELECT '100' PAT_ID, 1300 PLAN_ID, 'BRAVO' PLAN_NAME UNION
SELECT '200' PAT_ID, 1200 PLAN_ID, 'ALPHA' PLAN_NAME UNION
SELECT '300' PAT_ID, 1200 PLAN_ID, 'ALPHA' PLAN_NAME UNION
SELECT '200' PAT_ID, 8888 PLAN_ID, 'ZULU' LOC_ID
)
SELECT A.PAT_ID, A.PLAN_ID, A.PLAN_NAME
,'-' [__] , B.PLAN_ID, B.PLAN_NAME
FROM CTE A
JOIN CTE B ON A.PAT_ID = B.PAT_ID
AND B.PLAN_ID = 8888
WHERE A.PLAN_ID = 1200
September 28, 2015 at 11:22 am
You are SELECTing a.*, b.*
In other words you are showing data from two source rows in one result row. You must use a join.
September 28, 2015 at 11:32 am
Thanks Bill,
so it will work then,
what if I don't need B.*, I'll be fine with only A.* can it be done without JOIN?
M
September 28, 2015 at 11:52 am
You could go for something like this:
WITH CTE AS (
SELECT '100' PAT_ID, 1200 PLAN_ID, 'ALPHA' PLAN_NAME UNION
SELECT '100' PAT_ID, 1300 PLAN_ID, 'BRAVO' PLAN_NAME UNION
SELECT '200' PAT_ID, 1200 PLAN_ID, 'ALPHA' PLAN_NAME UNION
SELECT '300' PAT_ID, 1200 PLAN_ID, 'ALPHA' PLAN_NAME UNION
SELECT '200' PAT_ID, 8888 PLAN_ID, 'ZULU' LOC_ID
)
SELECT A.PAT_ID
FROM CTE A
WHERE A.PLAN_ID IN( 1200, 8888)
GROUP BY PAT_ID
HAVING COUNT( DISTINCT PLAN_ID) = 2;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply