2 or 3 results combined

[frankvanlogten]

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

[AndreyZ]

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;

[frankvanlogten]

Hello,

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

[AndreyZ]

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

[frankvanlogten]

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