asked
When I run a case statement, i am getting 2 rows for the matching data. One for the match and another for null value
select distinct (right(replace(b.bookingnumber,'-00',''),8)) 'BookingID', g.localidnumber 'PermanentID', left(replace(g.lastname, '''', ''),25) as 'Last Name', left(replace(g.firstname, '''', ''),15) as 'First Name', left(replace(g.MiddleName, '''', ''),15) as 'Middle Name', p.PodBlockCode as 'Location 1', (case when ((ci.StartDate <= GETDATE() or ci.EndDate >= GETDATE()) and v.EntryValue like '%commissary') then 'R' else c.CellCode end) as 'Location 2', bd.bedname as 'Location 3', convert(char(8),b.bookingdatetime,112) as 'Admit Date', replace(convert(char(5),b.bookingdatetime,108), ':', '') as 'Admit Time', left(v2.Description, 1) as Gender, convert(char(8),g.DateOfBirth, 112) as DOB --v.EntryValue, --ci.startdate, --ci.enddate, from Booking b join GlobalJacket g on b.JacketID = g.JacketID left join BookingRelease br on b.BookingID = br.BookingID join Bed bd on bd.BedID = b.BedID join Cell c on c.CellID = bd.CellID join PodBlock p on p.PodBlockID = c.PodBlockID join Facility f on f.FacilityID = p.FacilityID left join ValidationSetEntry v2 on v2.EntryID = g.vsSex left join correctionsincidentsubject cs on cs.bookingid = b.bookingid left join correctionsincidentsubjectviolation cv on cv.subjectid = cs.subjectid left join corrincsubjectviolationdiscipline cd on cd.violationid = cv.violationid left join corrincsubjectdiscipline ci on ci.disciplineid = cd.disciplineid left join validationsetentry v on ci.vsdisciplinetype = v.entryid and v.setid = '1130' and v.EntryID = '-25914' where b.HousedFlag = 1 and b.ORI = 'CA0380000' and the result set contains: BookingID PermanentID Last Name First Name Middle Name Location 1 location2 Location 3 Admit Date Admit Time Gender DOB 01234567 123456 HANLEY THOMAS Z 4M TANK J1 4MJ1 4MJ1-08 20040416 0130 M 19850626 02345678 567890 MARROQUIN FROILAN NULL 4M BLOCK B1 4MB1 4MB1-01 20080914 1629 M 19760925 03456789 485964 GILLIS DONALD NULL 5M POD 7B 5M7B 5M7B04T 20090604 0727 M 19700202 09876540 666661 MARTINEZ MARTIN NULL 4M BLOCK B4 4MB4 4MB4-09 20081025 0830 M 19890322 09876540 666661 MARTINEZ MARTIN NULL 4M BLOCK B4 R 4MB4-09 20081025 0830 M 19890322 02410155 584332 BARNES BARBARA NULL 2F POD B 2FB LOWER 2FBL35T 20090314 1131 F 19841015 02424241 522222 FEE JAVON NULL 4M BLOCK B6 4MB6 4MB6-12 20090401 1751 M 19800319 02424241 522222 FEE JAVON NULL 4M BLOCK B6 R 4MB6-12 20090401 1751 M 19800319 02410235 531860 REYNA RORICO NULL 4M BLOCK C5 4MC5 4MC5-01 20090315 0457 M 19770804 02421870 623102 GUTIERREZ TOMAS G 4M BLOCK B4 4MB4 4MB4-07 20090603 2145 M 19880911 02421875 551003 KAZARIAN SIERRA A 2F POD B 2FB LOWER 2FBL21T 20090603 2315 F 19790406 09876543 333000 HERRERA ALEX NULL 5M POD 5A 5M5A 5M5A22T 20090910 1950 M 19571120 09876543 333000 HERRERA ALEX NULL 5M POD 5A R 5M5A22T 20090910 1950 M 19571120 I need to only display the rows that have Location 2 as 'R' if it matches the case statement if not whatever the location 2 is. Thanks for your help with this.
The case statement won't act as a filter when placed in the SELECT clause. If an inmate has more than 1 matching row in corrincsubjectdiscipline table, or one of the other c* tables, you'll see the rows from the left side of the join duplicate to fill out the result set. If there are more rows in the left tables than in the c* tables, you will see the null values from the right duplicate to fill out the result set. I can't tell at a glance where the duplicates are coming from, but it may be near the many-to-many looking relationship between correctionsincidentsubject and corrincsubjectdiscipline. If you only want results for a single discipline row, you'll have to aggregate across all the c* tables then group by bookingId. If the problem is coming higher up the table list, and you really need to get rid of the duplicate rows, you can aggregate some of those tables. A quick and dirty way to get the results you are after without doing the work to identify the cause of the duplicates is to put your entire query into a subquery then select the max of every column and group by BookingId and/or PermanentId. This only works if c.CellCode always stars with a character representation of a digit ('0' through '9') because 'R' is always greater than '9'
Thank you KenJ. I will try to do it as a sub query. For now I did a union and except to get the rows I need and it seems to be working. I don't know if I was clear in my question. I need all the rows and ones that match the case statement filter, I want to replace location2 with 'R'. If they don't match, then it should be whatever is in Location2. c.cellcode doesn't always start with a digit so I can't do a max
15 People are following this question.
