2 or 3 results combined

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
frankvanlogten
Posts: 7
Joined: Sat 21 May 2011 09:18

2 or 3 results combined

Post by frankvanlogten » 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

AndreyZ

Post by AndreyZ » 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;

frankvanlogten
Posts: 7
Joined: Sat 21 May 2011 09:18

Post by frankvanlogten » Wed 08 Jun 2011 14:32

Hello,

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

AndreyZ

Post by AndreyZ » 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

frankvanlogten
Posts: 7
Joined: Sat 21 May 2011 09:18

Post by frankvanlogten » Thu 09 Jun 2011 09:46

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

Post Reply