Threaded SQL query with string decoding

[graabein]

Hi!

I'm experiencing query problems! My Procedure table looks like this:

ID  | Patient | Date       | Procedures | SaveDate
130 | 1532    | 2002-11-20 | 1;         | 2004-09-20
131 | 723     | 2002-11-20 |            | 2004-09-20
132 | 792     | 2002-11-04 | 3;17;      | 2004-09-20

And the ProcedureList table looks like:

ID | Name         | Active
1  | Day care     | 1
2  | Lobotomy     | 0
3  | Surgery      | 1
17 | Consultation | 1

I want to list registred records for a specific patient by passing a SQL query to the database that returns:

ID  | Date       | ProcedureNames | SaveDate
130 | 2002-11-20 | Day care       | 2004-09-20

for patient number 1532.

Another example for patient number 723:

ID  | Date       | ProcedureNames | SaveDate
131 | 2002-11-20 |                | 2004-09-20

And the last one (number 792):

ID  | Date       | ProcedureNames        | SaveDate
132 | 2002-11-04 | Surgery, consultation | 2004-09-20

I can't seem to crack the nested select and case subquery... any tips and pointers would be very welcome!


graabein

 

[graabein]

Nevermind. I did it in two steps instead. Not much difference...

 

[PruSQLer]

This is a moot point now but that "Procedure" column is a prime example of poor RDB design. Repeating groups should be broken out into separate entities at the first level of normalization.

But I realize us "data fetchers" are often left to work with what we inherit.