Pull one record based on 2 conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Pull one record based on 2 conditions

Author

Topic

ALSZ37
Starting Member

25 Posts

Posted - 2014-10-03 : 02:07:39

Good Evening all,

I have 3 main tables and then some lookup tables I have to join that define certain fields in the main tables. Table A holds the patients personal data, Table B tracks the program the patient is in, Table C tracking the listing with a location column.

So if a patient was in 2 programs, and had 1 listing with 1 program and 2 listings with the other program there would be 1 patient record in Table A, 2 in table B for each program, and 3 in table C, 1 listing tied to 1 program and the other tied to 2 programs.

My problem is I only want to show patients that have a listing in facility id 245 & 546. So if they have a listing at both facilities I want it to pull the patient on my report. I have all the data elements, but I'm having trouble with the condition. When I put where facility_ID = 245 and facility_ID = 546 nothing displays.

Table A = patient
Table B = program
Table C = listing table

Orsla
Starting Member

5 Posts

Posted - 2014-10-03 : 03:19:51

The more accurate description you give of your problem the more accurate answer you are going to get so.. post some sample data with the table structures so we see where the problem is.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-10-03 : 05:51:24

[code]
WHERE facility_ID IN (245, 546)
[/code]

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-10-03 : 10:29:10

Your code would be nice to see when trying to help.
I might suggest a two part cte that finds the patients in 245 then finds patients also in 546.

djj

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-03 : 11:55:27

If a given facility id can appear only once for a given patient in tableC, you can simplify the HAVING to just: HAVING COUNT(*) = 2


SELECT a.*
FROM tableA a
INNER JOIN (
    SELECT c.patient_id
    FROM tableC c
    WHERE c.facility_id IN (245, 546)
    GROUP BY c.patient_id
    HAVING MAX(CASE WHEN c.facility_id = 245 THEN 1 ELSE 0 END) = 1 AND
        MAX(CASE WHEN c.facility_id = 546 THEN 1 ELSE 0 END) = 1
) AS both_245_and_546 ON
    both_245_and_546.patient_id = a.patient_id

ALSZ37
Starting Member

25 Posts

Posted - 2014-10-06 : 14:37:50

Thank you all!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources