I'm trying to make a report for someone's poorly designed database and can't figure out how to handle this situation. This is in MSSQL. I searched for an answer to what I needed and couldn't find this scenario. When I run the following query:
SELECT
a.episode,
a.SUL,
b.element
FROM TableA as a
LEFT JOIN TableB as b ON (a.ElementKey = b.ElementKey)
WHERE a.episode = '99999'
It returns results that look like this:
| episode | SUL | element |
|---------|-----|---------|
| 99999 | 0 | SLC |
| 99999 | 1 | SL 1 |
I need them to look like:
| episode | substation | team |
|---------|------------|------|
| 99999 | SLC | SL1 |
The database basically has 2 records for each record. 1 that has a SUL = 0 (with a element name) and 1 with a SUL = 1 with a (with a team name). I've tried joins and groupBy's and case statements, but nothing that gets me what I need. Thanks!
Edit: More information.
TABLE A:
| Name | Type |
|------------|--------|
| episode | bigint |
| SUL | int |
| elementKey | bigint |
Table B:
| Name | Type |
|------------|---------|
| elementKey | bigint |
| element | varchar |
I didn't do a good job of explaining. Basically every 'episode' on 'Table A' will have 2 rows. One with a SUL = 0 and one with SUL = 1. If it is equal to 0 then I get the 'Table B' element and assign it as 'Substation'. If it is equal to 1 then I get the 'Table B' element and assign it as 'Team'