Nested SQL subqueires

[Tommy.b]

Hello

So, recently I obtained a horrid assignment from lé boss, and I have yet to figure out how to do this.

The idea is to select the ID(db_medlem) of all customers that have purchased for over 1000, in the year 2010. (I know its a funny syntax, but its all I have to work with.)

kunder = the table with all client information
faklin = table with all invoice lines
So I figured, first I'd search all customers through ( I've limited myself to 5 to make it fast to run while developing it). Anything inside of [founditems] and [/founditems] will run for those 5 results.

So in the second SQL, i chose the one customer that has an invoice date larger than 2010, and where the db_medlemsnr(clientIDnr) is the one found in the first SQL.

Again anything inside of [foundItems][/founditems] will run for any results of this sql
Finally, I select the combined price of all invoices sum(db_pris) as totalPrice where the clientId is a clientID that Ifound in the most outerloop. But this doesnt work.

[SQL dsn=[unurl][dsn][/unurl]&statement=select top 5 * from kunder]
	Found [NumFound] items<br>
	[FoundItems]
		[SQL dsn=[unurl][dsn][/unurl]&statement=select top 1 * from kunder where '2010' > (select max(LEFT(db_bestiltdato,4)) from faklin where db_medlemsnr = '[db_medlem]') and db_medlem ='[db_medlem]']
			[foundItems]
				[SQL dsn=[unurl][dsn][/unurl]&statement=select sum(db_pris) as totalPrice from faklin where db_medlemsnr = [db_medlem]]		
					[foundItems]
						
						Client nr: [db_medlem] purchased for more than : ([totalPrice])<br />
						
					[/foundItems]
					
				[/sql]
				
			[/foundItems]
		[/sql]
	[/FoundItems]
[/SQL]

Anyone have any ideas as to how I get this to work, I though about selecting all invoice lines first, but I require a date, and a clientID for that, which I wont have unless I run the outerloop first and select the clientIDs. In other words, Im completely stuck, aside from the grotesque syntax, anyone have any ideas?

Regards
Tommy

[tesuji]

Hi Tommy,

greetings from the hood.

If I understand you correctly, you want to list all customers which have purchased goods for more than 5000EUR in 2010. This query should solve it (you need to substitute in your appropriate column names):

WITH totalAmount (Kunder, ID, Amount) as
(
  SELECT k.kunderName as "Kunder", k.kunderID as "ID", 
   SUM(f.Quantity * f.Itemprice) as Amount from kunder k join faklin f 
    ON k.kunderID = f.kunderID
     WHERE f.purchasedDate between '2010-01-01' AND '2010-12-31'
      GROUP BY k.kunderName, k.kunderID
)
SELECT * from totalAmount where Amount >= 5000 ORDER BY Amount desc;

Possibly you need to replace SUM(f.Quantity * f.Itemprice) by SUM(f.invoiceAmount), if you don't deal with invoice items.

Possibly you may also have to change date format. I took standard ISO date format yet your DB
may have other settings (Denmark's date format same as Bavaria's?)

-- tesu

[Tommy.b]

Excellent thanks for the suggestion, following your suggestion I came up with something that worked.

select top 10 DB_MEDLEMSNR, sum(DB_PRIS) as TOTALPRIS from faklin where
DB_FAKTURANR between '20100101000' and '20100201000'
and DB_FAKTURANR not like 'M%'
 group by DB_MEDLEMSNR
having sum(DB_PRIS) > 299
order by TOTALPRIS desc