Devart Forums

Posted: **Mon 06 Jun 2011 09:35**

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

Posted: **Tue 07 Jun 2011 07:29**

Hello,

You can combine several SQL queries in one query. Here is an example:

Code: Select all

MSQuery.SQL.Clear;
MSQuery.SQL.Add('select * from dept where deptno  20 and deptno < 50;');
MSQuery.Open;

You will obtain the result of the first query execution. To obtain the result of the next query, you should call the OpenNext method like this:

Code: Select all

MSQuery.OpenNext;

Posted: **Wed 08 Jun 2011 14:32**

Hello,

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

Posted: **Thu 09 Jun 2011 08:17**

Try using the following query:

Code: Select all

SELECT n.unieknummer, klantnummer, bedrijfsnaam, adres, postcode, plaats, branche, agent, verkoopprijs1, verkoopprijs2, verkoopprijs3
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 = '01-01-2010' 
AND opleverdatum = '01-01-2011' 
AND opleverdatum <= '31-12-2011' 
GROUP BY unieknummer ) 
AS p2 ON n.unieknummer = p2.unieknummer 
ORDER BY bedrijfsnaam

Posted: **Thu 09 Jun 2011 09:46**

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

Devart Forums

2 or 3 results combined

2 or 3 results combined