Original Data
| PKG | INDICES |
|---|---|
| I17 | 21 |
| I17 | 42 |
| I17 | 76 |
| I17 | BMAA |
| I17 | XW |
| I20 | 21 |
| I20 | 42 |
| I20 | 76 |
| I20 | BMAA |
| I20 | XW |
| I19 | 42 |
| I19 | 76 |
| I19 | XW |
Query: select * From fqipkg2 a left join fqipkg2 b on (a.pkg <> b.pkg and a.indices = b.indices) where a.pkg = 'i17' and (b.pkg = 'i19' or b.pkg is null)
This results in the following output: | PKG | INDICES | PKG | INDICES | | -------- | -------------- | -------- | -------------- | | I17 | 42 | I19 | 42 | | I17 | 76 | I19 | 76 | | I17 | XW | I19 | XW |
The desired output is | PKG | INDICES | PKG | INDICES | | -------- | -------------- | -------- | -------------- | | I17 | 21 | NULL | NULL | | I17 | 42 | I19 | 42 | | I17 | 76 | I19 | 76 | | I17 | BMAA | NULL | NULL | | I17 | XW | I19 | XW |
Interestingly, we get the desired output when we change the data set to: | PKG | INDICES | | -------- | -------------- | | I17 | 21 | | I17 | 42 | | I17 | 76 | | I17 | BMAA | | I17 | XW | | I19 | 42 | | I19 | 76 | | I19 | XW |
Is something wrong with the query? the data? any help appreciated!
CodePudding user response：
Move conditions on the left joined table out of the where and into the join condition:
select *
from fqipkg2 a
left join fqipkg2 b on a.pkg <> b.pkg
and a.indices = b.indices
and (b.pkg = 'i19' or b.pkg is null)
where a.pkg = 'i17'
The reason this works is that referencing the joined table in the where clause effectively converts the outer join to an inner join, because b.pkg must have a value to make the comparison b.pkg = 'i19', and only joined rows have values.
CodePudding user response：
The join does never leaves null indices on either side because you join on them. Look at the join table before the where clause:
a.pgk | joined on index | b.pkg
i20 | 21 | i17
i20 | 42 | i17
i20 | 42 | i19
i20 | 76 | i27
i20 | 79 | i19
i20 | BMAA | i17
i20 | XW | i17
i20 | XW | i19
i17 | 21 | i20
i17 | 42 | i20
i17 | 42 | i19 <- where a.pkg = 'i17' and (b.pkg = 'i19' ...)
i17 | 76 | i20
i17 | 76 | i19 <- where a.pkg = 'i17' and (b.pkg = 'i19' ...)
i17 | MBAA | i20
i17 | XW | i20
i17 | XW | i19 <- where a.pkg = 'i17' and (b.pkg = 'i19' ...)
i19 | 42 | i29
i19 | 42 | i17
i19 | 76 | i20
i19 | 76 | i17
i19 | XW | i20
i19 | XW | i17
@Bohemian beat me with coming up a corrected query but I'll leave this here just so you can understand better why the result was what it was.