 |
|||
An interesting JOIN and pairing problem
By: rekha singh | 18 May 2010 2:14 pm
Have a file with recs like ... 1,house, chicago
2,apt, detroit
3,house, miami
4,house, boston
5,apt, boston
6,house, houston
7,apt, dallas
8,house, detroit
9,house, boston
10,apt, detroit
11,apt, miami
We want to delete PAIRS of house/apt in the same city.
So we would pair (and delete) records:
2 & 8 (a house & apt in Detroit)
3 & 11 (a house & apt in Miami)
4 & 5 (a house & apt in Boston)
We cannot delete record 1 because there is no apt for Chicago
We cannot delete record 6 because there is no apt for Houston
We cannot delete record 7 because there is no house for Dallas
We cannot delete record 9 even though there IS an apt for Bostson because that apartment was already paired with record 4 (this is the part that I think SQL can't handle (ie. "knowing" that a record has already been "used")
We cannot delete record 10 even though there IS a house for Detroit because that house was already paired with record record 2
So the final report would list records
1, 6, 7, 9, 10
In SQL I would try something like :
SELECT RECNUM, CITY, TYPE
FROM MYFILE
WHERE RECNUM NOT IN
(SELECT A.RECNUM
FROM MYFILE A INNER JOIN MYFILE B
USING (CITY)
WHERE A.TYPE <> B.TYPE // luckily there are only 2 types
)
Predictably, it reports only
1, 6, 7
because it thinks records 9 & 10 have pairs
(9 & 10 do have pairs, but they were already paired ("used")
with other records).
Anybody know how SQL can get the right answer ?
|
