problem with left outer join

frankvanlogten · Post by **frankvanlogten** » Sat 21 May 2011 09:33

Hello,

I have a problem with left outer join command!
I don't get the result i need!
I got a table with naw (customers) and a table with project
I want to join these tables with the left outer join command.

sql code:

SELECT n.klantnummer , n.bedrijfsnaam, n.adres, postcode, plaats, branche, agent, n.unieknummer, p.aanvraagdatum, p.projectnummer,p.opleverdatum
FROM naw n
LEFT OUTER JOIN project p ON n.unieknummer = p.unieknummer
WHERE n.agent = 'Frank van Logten'
AND p.opleverdatum >= '01-01-2011'
ORDER BY bedrijfsnaam

The result only show the customers where agent is Frank van Logten and where the opleverdatum >= 01-01-2011
The customers of agent Frank van Logten who don't have opleverdatum >= 01-01-2011 are not shown in this result?!

What am i doing wrong?

P.s. I use the trail version of sdac for Delhi 2009
I'm using dbf files at the moment but want to switch to MSSQL and now i'm testing sdac

AndreyZ · Post by **AndreyZ** » Mon 23 May 2011 11:46

Hello,

Such behaviour is correct.
LEFT OUTER JOIN returns all data from the left table (according to the conditions in the WHERE clause). Data from the right table is returned for records that exist both in the left and right tables. If there are no records in the right table, NULL values are returned.
Please specify the exact result (for example, two simple tables with data and data of the resulting table) you want to get, and we will try to help you.

frankvanlogten · Post by **frankvanlogten** » Tue 24 May 2011 10:06

Hello,

After trying some thing i found out that i have to use a subquery for this.

However i'm having an other probleem now.
I'm using the next SQL code:

SELECT p.unieknummer, SUM(p.verkoopprijs) AS verkoopprijs, (CASE WHEN k.hoofdproductcategorie IS NULL OR k.hoofdproductcategorie='' THEN 'LEEG' ELSE k.hoofdproductcategorie end) AS hoofdproductcategorie
FROM project AS p
LEFT OUTER JOIN projectkaart
AS k ON p.projectnummer = k.projectnummer
WHERE offerte_status = 'Opgeleverd'
AND opleverdatum >= '01-01-2009'
AND opleverdatum <= '31-12-2009'
GROUP BY unieknummer, hoofdproductcategorie
ORDER BY unieknummer

Which gives me the next result:
unieknummer verkoopprijs hoofdproductcategorie
1220 2274 LEEG
1220 839 LEEG
1350 175 LEEG
1350 1513 LEEG
1350 290 full color
1429 65 LEEG
1429 265 LEEG
1439 175 LEEG
1557 2040 LEEG
1615 396 LEEG
1639 35 LEEG
1641 0 LEEG
1641 8226 LEEG
1641 2876 full color
1691 875 LEEG
1691 3154 LEEG
1691 158 full color
..................................................................

I want the following result:
unieknummer verkoopprijs hoofdproductcategorie
1220 3113 LEEG
1350 1688 LEEG
1350 290 full color
1429 330 LEEG
1439 175 LEEG
1557 2040 LEEG
1615 396 LEEG
1639 35 LEEG
1641 8226 LEEG
1641 2876 full color
1691 4029 LEEG
1691 158 full color
..................................................................

AndreyZ · Post by **AndreyZ** » Tue 24 May 2011 13:12

The reason you have this result is that rows with empty and null values in the hoofdproductcategorie column are grouped to different groups. To solve the problem, try using the following code:

Code: Select all

SELECT p.unieknummer, SUM(p.verkoopprijs) AS verkoopprijs,
(CASE WHEN k.hoofdproductcategorie IS NULL OR k.hoofdproductcategorie ='' THEN 'LEEG' ELSE k.hoofdproductcategorie end) AS hoofdproductcategorie
FROM project AS p 
LEFT OUTER JOIN projectkaart 
AS k ON p.projectnummer = k.projectnummer
WHERE offerte_status = 'Opgeleverd' 
AND opleverdatum >= '01-01-2009' 
AND opleverdatum <= '31-12-2009'
GROUP BY unieknummer, (CASE WHEN k.hoofdproductcategorie IS NULL OR k.hoofdproductcategorie ='' THEN 'LEEG' ELSE k.hoofdproductcategorie end)
ORDER BY unieknummer

frankvanlogten · Post by **frankvanlogten** » Tue 24 May 2011 15:28

Thanks.

That should do the trick