[MySQL v4.1] Probleem met outer join

[DPLuS]

Hoi,

Ik heb een probleem met een query.
Dit zijn de tabellen die ik gebruik:

code:

mysql> describe Keten;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| keten_naam | varchar(16)      |      | PRI |         |       |
| view_order | int(10) unsigned |      |     | 0       |       |
+------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from Keten;
+------------------+------------+
| keten_naam       | view_order |
+------------------+------------+
| ProblemManagers  |          0 |
| Support          |          1 |
| FieldEngineer    |          2 |
| IncidentEngineer |          3 |
| KlantIngang      |          4 |
+------------------+------------+
5 rows in set (0.00 sec)


mysql> describe Oplossing;
+------------------------+------------------+------+-----+---------------------+-------+
| Field                  | Type             | Null | Key | Default             | Extra |
+------------------------+------------------+------+-----+---------------------+-------+
| probleem_id            | int(10) unsigned |      | PRI | 0                   |       |
| aanmaak_datum          | datetime         |      |     | 0000-00-00 00:00:00 |       |
| oplossing_omschrijving | text             |      | MUL |                     |       |
| keten_naam             | varchar(16)      |      | PRI |                     |       |
+------------------------+------------------+------+-----+---------------------+-------+
4 rows in set (0.00 sec)

mysql> select * from Oplossing WHERE probleem_id = 2;
+-------------+---------------------+------------------------------------------------------------+------------------+
| probleem_id | aanmaak_datum       | oplossing_omschrijving                                     | keten_naam       |
+-------------+---------------------+------------------------------------------------------------+------------------+
|           2 | 2005-06-08 18:32:28 | IncidentEngineer                                           | IncidentEngineer |
|           2 | 2005-06-08 18:32:04 | KlantIngang                                                | KlantIngang      |
|           2 | 2005-06-08 16:21:00 | En we vullen hier gewoon al een oplossing in op PM-niveau. | ProblemManagers  |
+-------------+---------------------+------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

Het is een problem-management systeem waar per probleem_id oplossingen geboden kunnen worden op verschillende niveau's.
Die niveau's worden gerepresenteerd door de tabel Keten, waar een lager view_order hogere rechten betekent (a la linux, root == 0).

Nu heb ik de volgende query:

code:

SELECT Keten.keten_naam, Keten.view_order, Oplossing.aanmaak_datum, Oplossing.oplossing_omschrijving
FROM Keten LEFT OUTER JOIN Oplossing ON Oplossing.keten_naam = Keten.keten_naam
WHERE Keten.view_order >= (SELECT Keten.view_order FROM Keten WHERE Keten.keten_naam = "ProblemManagers") 
AND Oplossing.probleem_id = 2 OR Oplossing.probleem_id IS NULL
ORDER BY Keten.view_order ASC;

Met deze query wil ik bereiken dat ik in mijn resultaat alle keten_namen en view_orders te zien krijg die groter of gelijk zijn aan de view_order tot waartoe de gebruiker behoort (in deze query behoort 'ie dus tot de ProblemManagers).
En dan wil ik verder dat MySQL er twee velden uit de Oplossingen tabel aan koppelt (te weten aanmaak_datum en oplossing_omschrijving) voor de ketens waar een oplossing_omschrijving bestaat.

Dus met de bovenstaande data verwacht ik eigenlijk de volgende resultset:

code:

+------------------+------------+---------------------+------------------------------------------------------------+
| keten_naam       | view_order | aanmaak_datum       | oplossing_omschrijving                                     |
+------------------+------------+---------------------+------------------------------------------------------------+
| ProblemManagers  |          0 | 2005-06-08 16:21:00 | En we vullen hier gewoon al een oplossing in op PM-niveau. |
| Support          |          1 | NULL                | NULL                                                       |
| FieldEngineer    |          2 | NULL                | NULL                                                       |
| IncidentEngineer |          3 | 2005-06-08 18:32:28 | IncidentEngineer                                           |
| KlantIngang      |          4 | 2005-06-08 18:32:04 | KlantIngang                                                |
+------------------+------------+---------------------+------------------------------------------------------------+
5 rows in set (0.00 sec)

Maar helaas krijg ik deze resultset terug:

code:

+------------------+------------+---------------------+------------------------------------------------------------+
| keten_naam       | view_order | aanmaak_datum       | oplossing_omschrijving                                     |
+------------------+------------+---------------------+------------------------------------------------------------+
| ProblemManagers  |          0 | 2005-06-08 16:21:00 | En we vullen hier gewoon al een oplossing in op PM-niveau. |
| IncidentEngineer |          3 | 2005-06-08 18:32:28 | IncidentEngineer                                           |
| KlantIngang      |          4 | 2005-06-08 18:32:04 | KlantIngang                                                |
+------------------+------------+---------------------+------------------------------------------------------------+
3 rows in set (0.00 sec)

Dus het ziet er naar uit dat als er geen overeenkomstige oplossing uit dezelfde keten is in de Oplossing-tabel (IS NULL), dat 'ie de rest maar helemaal eruit filtert.
Hoe kan ik ervoor zorgen dat 'ie toch ALLE KETENS gewoon weergeeft, en daar waar er oplossingen zijn, dat 'ie ze ook laat zien?


Nog wat relevante data:
Ik draai MySQL 4.1.11 uit de Debian Testing branche.
Ik maak van de OUTER JOIN gebruik omdat ik het volgende las op: http://mysqld.active-venture.com/IS_NULL_optimisation.html

If you use column_name IS NULL on a NOT NULL in a WHERE clause on table that is not used OUTER JOIN that expression will be optimised away

[DPLuS]

Ah, naar aanleiding van dit topic:
[rml][ SQL] select 2 tabellen, ook lege kolommen zien[/rml]
ben ik erachter gekomen dat je die IS NULL vergelijking niet mag doen in een WHERE clause.
Die wordt al automatisch meegenomen in de LEFT OUTER JOIN.

Query is nu herschreven naar:

code:

SELECT Keten.keten_naam, Keten.view_order, Oplossing.oplossing_omschrijving
FROM Keten LEFT OUTER JOIN Oplossing ON Oplossing.keten_naam = Keten.keten_naam
AND Oplossing.probleem_id = 2
WHERE Keten.view_order >= (SELECT Keten.view_order FROM Keten WHERE Keten.keten_naam = "ProblemManagers") 
ORDER BY Keten.view_order ASC

en dit werkt "as expected"!