Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Imagine the following table...
- -- --------------------
- -- |ID|NAME|VAL |
- -- |==|====|============|
- -- |A |KEY1|1.0000000000|
- -- |A |KEY2|2.0000000000|
- -- |B |KEY1|3.0000000000|
- -- |B |KEY2|4.0000000000|
- -- |B |KEY3|5.0000000000|
- -- |C |KEY1|6.0000000000|
- -- |D |KEY2|7.0000000000|
- -- --------------------
- -- You can flatten out by INNER JOINing. For example...
- SELECT a.ID, a.VAL, b.VAL FROM
- (SELECT * FROM TEST_TBL WHERE NAME='KEY1') a
- INNER JOIN
- (SELECT * FROM TEST_TBL WHERE NAME='KEY2') b
- ON a.ID=b.ID;
- -- Will result in...
- -- ----------------------------
- -- |ID|VAL |VAL |
- -- |==|============|============|
- -- |A |1.0000000000|2.0000000000|
- -- |B |3.0000000000|4.0000000000|
- -- ----------------------------
- -- Note that 'A' and 'B' are there because they both contain an entry for 'KEY1' and 'KEY2, but...
- -- 'C' is missing because it's missing 'KEY2'
- -- 'D' is missing becuase it's missing 'KEY1'
- -- So what if we want to include 'C' and 'D'? The subqueries in our original query need to be changed to include all IDs. So for
- -- example, for 'KEY1', LEFT JOIN the all IDs with the IDs that have 'KEY1'...
- SELECT a.ID, b.VAL FROM
- (SELECT DISTINCT ID FROM TEST_TBL) a -- set of all IDs
- LEFT JOIN
- (SELECT ID, VAL FROM TEST_TBL WHERE NAME='KEY1') b -- set of IDs/VALs that contain 'KEY1'
- ON a.ID = b.ID;
- -- Will result in...
- -- ---------------
- -- |ID|VAL |
- -- |==|============|
- -- |A |1.0000000000|
- -- |B |3.0000000000|
- -- |C |6.0000000000|
- -- |D |<null> |
- -- ---------------
- -- Once you substitute these new subqueries in...
- SELECT a.ID, a.VAL, b.VAL FROM
- (SELECT aa.ID, ab.VAL FROM (SELECT DISTINCT ID FROM TEST_TBL) aa LEFT JOIN (SELECT ID, VAL FROM TEST_TBL WHERE NAME='KEY1') ab ON aa.ID = ab.ID) a
- INNER JOIN
- (SELECT ba.ID, bb.VAL FROM (SELECT DISTINCT ID FROM TEST_TBL) ba LEFT JOIN (SELECT ID, VAL FROM TEST_TBL WHERE NAME='KEY2') bb ON ba.ID = bb.ID) b
- ON a.ID=b.ID;
- -- You'll get the correct results..
- -- ----------------------------
- -- |ID|VAL |VAL |
- -- |==|============|============|
- -- |A |1.0000000000|2.0000000000|
- -- |B |3.0000000000|4.0000000000|
- -- |C |6.0000000000|<null> |
- -- |D |<null> |7.0000000000|
- -- ----------------------------
Add Comment
Please, Sign In to add comment