---
title: "LEFT join probz."
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/147732-left-join-probz"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "rincewind"
published: "2006-06-05T13:12:38.000Z"
updated: "2006-06-05T13:33:13.000Z"
replies: 2
views: 381
page: 1
pages: 1
language: "sv-SE"
site: "webForum — webforum.nu"
rights: "Upphovsrätten till varje inlägg tillhör dess författare."
attribution: "Citera som: webForum, https://www.webforum.nu/amne/databaser-sql/147732-left-join-probz"
---

# LEFT join probz.

## #1 — rincewind, 2006-06-05T13:12Z

Hej, 
jag har följande taballer..

// usertable
ID		\| username
\_____\_\|\_________\_
1			\| user1
2			\| user2
3 		\| user3

// gemstable
ID		\| won		 \| lost		\| userID	\|	onDate
\_____\_\|\_______\_\|\_______\_\|\________\_\|\_____\_
1			\|	3			 \| 1			\| 1				\|	2005-04-22
2			\|	1			 \| 2			\| 1				\|	2001-11-02
3			\|	6			 \| 4			\| 2				\|	2002-01-16
4			\|	1			 \| 2			\| 1				\|	2006-01-12

Om jag gör en 
SELECT usertable.username as userName, SUM(gemstable.won) AS wonSum, SUM(gemstable.lost) AS lostSum FROM usertable LEFT JOIN gemstable ON usertable.ID=gemstable.userID GROUP BY usertable.ID
Ger mig svaret:

userName	\| wonSum	\| lostSum 
\_________\_\|\________\_\|\_______\_
user1 		\| 5				\| 5
user2 		\| 6				\| 4
user3 		\| NULL		\| NULL

Om jag gör en:
SELECT usertable.username as userName, SUM(gemstable.won) AS wonSum, SUM(gemstable.lost) AS lostSum FROM usertable LEFT JOIN gemstable ON usertable.ID=gemstable.userID WHERE gemstable.onDate\<='2002-01-31' AND gemstable.onDate\>='2002-01-01' GROUP BY usertable.ID
dvs lägger till WHERE biten får jag svaret:

userName	\| wonSum	\| lostSum 
\_________\_\|\________\_\|\_______\_
user2 		\| 6				\| 4

fast vad jag skulle vilja ha är:

userName	\| wonSum	\| lostSum 
\_________\_\|\________\_\|\_______\_
user1 		\| NULL		\| NULL
user2 		\| 6				\| 4
user3 		\| NULL		\| NULL

dvs att jag får ALLTID får allt från 'usertable' men summeringen av 'lost' och 'won' sker bara över det givna tids-spannet...
Någon som kan denna sql fråga?

Tacksam för svar.
 \-- R

Permalänk: https://www.webforum.nu/p/147732

## #2 — @nders, 2006-06-05T13:15Z

Flytta villkoren från WHERE till join-instruktionen. Dvs:

```
SELECT usertable.username as userName, SUM(gemstable.won) AS wonSum, SUM(gemstable.lost) AS lostSum 
FROM usertable 
LEFT JOIN gemstable ON usertable.ID=gemstable.userID [b]AND[/b] 
gemstable.onDate<='2002-01-31' AND gemstable.onDate>='2002-01-01' 
GROUP BY usertable.ID
```

Mvh

Permalänk: https://www.webforum.nu/p/1826689

## #3 — rincewind, 2006-06-05T13:33Z

hoho.. tack @nders.. rekord snabbt svar också.!

Permalänk: https://www.webforum.nu/p/1826696

---

Tråden på webben: https://www.webforum.nu/amne/databaser-sql/147732-left-join-probz