pv0RYp7F

1id   name  budget
21    John   1000
32    Kim    3000
4	
5id   amount
61    112
71    145
81    211
9	
10id  name   budget amount
111   John   1000   112
121   null   null   145
131   null   null   211
142   Kim    3000   null
15	
16id  name   budget amount
171   null   null   112
181   John   1000   145
191   null   null   211
202   Kim    3000   null
21	
22create temporary table a (id1 int,name varchar(10),budget int);
23insert into a (id1,name,budget) values(1,'Maier',1000),(2,'Mueller',2000);
24create temporary table if not exists b (id2 int,betrag int);
25insert into b (id2,betrag) values(1,100),(1,133),(1,234);
26select * from a left join b
27on a.id1=b.id2
28;
29	
30select distinct b.id, b.name, b.budget, s.amount
31from budgets b left join spendings s 
32on b.id = s.id;
33	
34select b.id, b.name, b.budget, s.amount
35from budgets b left join spendings s 
36on b.id = s.id
37group by b.id, b.name, b.budget, s.amount;
38	
39create  table a (id1 int,name varchar(10),budget int)
40insert into a (id1,name,budget) values(1,'Maier',1000)
41insert into a (id1,name,budget) values(2,'Mueller',2000)
42
43create  table b (id2 int,betrag int)
44insert into b (id2,betrag) values(1,100)
45insert into b (id2,betrag) values(1,133)
46insert into b (id2,betrag) values(1,234)
47insert into b (id2,betrag) values(2,300)
48insert into b (id2,betrag) values(2,400)
49
50select a.id1, CASE WHEN c.themin IS NOT NULL THEN a.name ELSE NULL END AS [name],
51CASE WHEN c.themin IS NOT NULL THEN a.budget ELSE NULL END AS [budget],
52b.*
53from a 
54LEFT join b on a.id1=b.id2
55LEFT OUTER JOIN (SELECT MIN(betrag) AS [themin], id2 FROM b GROUP BY id2) c ON a.id1 = c.id2 AND b.betrag = c.themin