Devart Forums

Thanks.
That should do it and what I was looking for...

Hello,

Is it also possible to use a join on those several query's?

Hello,

I have a question.
Is it possible to combine 2 or 3 result in 1 query?

I have got the following query:

SELECT n.unieknummer, klantnummer, bedrijfsnaam, adres, postcode, plaats, branche, agent, n.unieknummer, verkoopprijs1
FROM naw n
LEFT OUTER JOIN
(SELECT p.unieknummer, ROUND(SUM(p.verkoopprijs),0) AS verkoopprijs1
FROM project AS p
WHERE offerte_status = 'Opgeleverd'
AND opleverdatum >= '01-01-2009'
AND opleverdatum <= '31-12-2009'
GROUP BY unieknummer )
AS p ON n.unieknummer = p.unieknummer
ORDER BY bedrijfsnaam


Which give me a result on verkkoopprijs1 specified in date 2009
I want also the result of 2010 as verkoopprijs2 and 2011 as verkoopprijs3

Now i do this in separate query's

Thanks.

That should do the trick

Hello,

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
..................................................................

However 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
..................................................................

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
..................................................................

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