Why do I get NULL on this query?

I want to get the max values from kullnr where the values of ras are 1, 2 and 3. But when I run this query, I get NULL on all three. Here's the code:

SELECT MAX( k1.kullnr ) , MAX( k2.kullnr ) , MAX( k3.kullnr )
FROM kull AS k1
JOIN kull AS k2 ON k1.kullnr = k2.kullnr
JOIN kull AS k3 ON k1.kullnr = k3.kullnr
WHERE k1.ras =0
AND k2.ras =1
AND k3.ras =2 

If I run queries separated from each other, they work fine. For example:

SELECT MAX(kullnr) FROM kull WHERE ras=0

But due to extreme开发者_高级运维 memory limitations, I can't run the queries separated. How can I make the single-query version work?

---

Unless all your maximum kullnr fields have the same value, you will get null because you have joined on the field you are trying to maximize. I think this is more what you want:

SELECT k1.kullnr, k2.kullnr, k3.kullnr
FROM
(SELECT MAX(kullnr) AS kullnr FROM kull WHERE ras = 0) k1,
(SELECT MAX(kullnr) AS kullnr FROM kull WHERE ras = 1) k2,
(SELECT MAX(kullnr) AS kullnr FROM kull WHERE ras = 2) k3

I really don't think I would do this in this way, though, if I could help it. If you do not need all the maximums in the same row, another query might be:

SELECT   ras, MAX(kullnr)
FROM     kull
GROUP BY ras
HAVING   ras = 0 OR ras = 1 OR ras = 2

---

Looking at your separate query

SELECT MAX(kullnr) FROM kull WHERE ras=0

You need

SELECT MAX(CASE WHEN ras = 0 THEN kullnr END  ) , 
       MAX(CASE WHEN ras = 1 THEN kullnr END  ) ,
       MAX(CASE WHEN ras = 2 THEN kullnr END  )
FROM kull 
WHERE ras  IN (0,1,2)

You are joining on kullnr so for each row all self joined tables will have the same value for this field but then you are selecting the max value from each of the three tables for this field. If the JOIN returns any rows at all then the max would be the same across all 3 columns.

---

The query from Martin will get you the results you want.

As to WHY you got a null, the JOIN conditions are probably preventing any set of three rows from matching. They actually restrict the query to rows where the same kullnr is present in ras=0, ras=1 and ras=2