MySQL derived table where clause with value from parent qry

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL derived table where clause with value from parent qry

Post by batfastad »

Hi everyone

I've developed an intranet orders/billing system which has a report which at any point in time outputs the totals of all orders that are yet to be billed, a forward order book total. So the client knows how much money is in the pipeline that's yet to be billed.

However I now need to create a similar report which shows how the forward order book total changes over time.

Here's the single query that outputs my current forward order totals...

Code: Select all

SELECT 
SUM(CASE WHEN `adbookings`.`currency` = 'USD' AND `publications`.`sales_category` LIKE '%AMD%' THEN `adinserts`.`rate` ELSE NULL END) AS `amd_usd`, 
SUM(CASE WHEN `adbookings`.`currency` = 'EUR' AND `publications`.`sales_category` LIKE '%AMD%' THEN `adinserts`.`rate` ELSE NULL END) AS `amd_eur`, 
SUM(CASE WHEN `adbookings`.`currency` = 'GBP' AND `publications`.`sales_category` LIKE '%AMD%' THEN `adinserts`.`rate` ELSE NULL END) AS `amd_gbp`, 
SUM(CASE WHEN `adbookings`.`currency` = 'USD' AND `publications`.`sales_category` LIKE '%IDN%' THEN `adinserts`.`rate` ELSE NULL END) AS `idn_usd`, 
SUM(CASE WHEN `adbookings`.`currency` = 'EUR' AND `publications`.`sales_category` LIKE '%IDN%' THEN `adinserts`.`rate` ELSE NULL END) AS `idn_eur`, 
SUM(CASE WHEN `adbookings`.`currency` = 'GBP' AND `publications`.`sales_category` LIKE '%IDN%' THEN `adinserts`.`rate` ELSE NULL END) AS `idn_gbp` 
FROM `adinserts`
LEFT JOIN `adbookings` USING(`booking_id`)
LEFT JOIN `invoices` ON `adinserts`.`invoice_id`=`invoices`.`invoice_id`
LEFT JOIN `publications` ON `adinserts`.`publication_id`=`publications`.`publication_id`
WHERE `adbookings`.`booking_stamp`>=1230768000 AND `adbookings`.`booking_stamp`<UNIX_TIMESTAMP() AND `adinserts`.`rate`>0 AND `adinserts`.`cancel_stamp` IS NULL AND (`invoices`.`invoice_stamp` IS NULL OR `invoices`.`invoice_stamp`>=UNIX_TIMESTAMP()) AND `publications`.`sales_category` NOT LIKE '%DW%' AND `publications`.`sales_category` LIKE '%ISSUE%'
So this outputs the totals for 2 products (amd/idn) and 3 currency totals for each.
Note the lack of a GROUP BY clause so the query just outputs the totals.
The date/time fields are all stored as unix timestamps in the DB (don't ask!) hence the UNIX_TIMESTAMP() function in the where clause to get the current time stamp. That basically excludes any orders that havn't been booked yet, or that have already been invoiced.
The first part of the WHERE clause, booking_stamp>=1230768000 simply narrows the forward order book total to all orders placed after 1st Jan 2009, as there are orders before that time period that won't have invoices in the system therefore skewing our totals.

One way to get the results of this query over time would be to loop the query in PHP for every month from 2009-01 to the current month, and pass the timestamp for the end of that month to the query instead of UNIX_TIMESTAMP() in the WHERE clause. But that would mean looping through the query 24-36 times... a horrible way of doing things.

I was wondering, is there a way to convert the query above into some sort of derived table or sub-query?
The problem being... how does the sub-query know the timestamp (year-month) that the parent query is currently iterating through?
So the values in the WHERE clause (currently where UNIX_TIMESTAMP() is) have to somehow be passed in from the parent query.

Is something like that even possible, or am I stuck with looping through this query in PHP and running it for each year-month?

Does this make any sense at all?

Cheers, B
Last edited by batfastad on Mon Jan 31, 2011 6:46 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL derived table where clause with value from parent

Post by VladSun »

Code: Select all

SELECT
	test1.id,
	(
		select 
			test2.id 
		from 
			test2 
		where 
			test2.id = test1.id
	) as test2_id
FROM
	test1
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL derived table where clause with value from parent

Post by batfastad »

Ah right, ok so I can use the name of a table from the outer query within the sub-query.

But when I try and nest the query like this...

Code: Select all

SELECT FROM_UNIXTIME(`exrates`.`exrate_stamp` , '%Y%m') AS `month`, 
(SELECT 
SUM(CASE WHEN `adbookings`.`currency`='USD' AND `publications`.`sales_category` LIKE '%AMD%' THEN `adinserts`.`rate` ELSE NULL END) AS `amd_usd`, 
SUM(CASE WHEN `adbookings`.`currency`='EUR' AND `publications`.`sales_category` LIKE '%AMD%' THEN `adinserts`.`rate` ELSE NULL END) AS `amd_eur`, 
SUM(CASE WHEN `adbookings`.`currency`='GBP' AND `publications`.`sales_category` LIKE '%AMD%' THEN `adinserts`.`rate` ELSE NULL END) AS `amd_gbp`, 
SUM(CASE WHEN `adbookings`.`currency`='USD' AND `publications`.`sales_category` LIKE '%IDN%' THEN `adinserts`.`rate` ELSE NULL END) AS `idn_usd`, 
SUM(CASE WHEN `adbookings`.`currency`='EUR' AND `publications`.`sales_category` LIKE '%IDN%' THEN `adinserts`.`rate` ELSE NULL END) AS `idn_eur`, 
SUM(CASE WHEN `adbookings`.`currency`='GBP' AND `publications`.`sales_category` LIKE '%IDN%' THEN `adinserts`.`rate` ELSE NULL END) AS `idn_gbp` 
FROM `adinserts`
LEFT JOIN `adbookings` USING(`booking_id`)
LEFT JOIN `invoices` ON `adinserts`.`invoice_id`=`invoices`.`invoice_id`
LEFT JOIN `publications` ON `adinserts`.`publication_id`=`publications`.`publication_id`
WHERE `adbookings`.`booking_stamp`>=1230768000 
AND `adbookings`.`booking_stamp`<`exrates`.`exrate_stamp_end` 
AND `adinserts`.`rate`>0 AND `adinserts`.`cancel_stamp` IS NULL 
AND (`invoices`.`invoice_stamp` IS NULL OR `invoices`.`invoice_stamp`>=`exrates`.`exrate_stamp_end`) 
AND `publications`.`sales_category` NOT LIKE '%DW%' AND 
`publications`.`sales_category` LIKE '%ISSUE%') 
FROM `exrates`
WHERE `exrates`.`exrate_stamp`>=1230768000
ORDER BY `exrates`.`exrate_stamp`
I get an error... #1241 - Operand should contain 1 column(s)
I think the problem is caused by my comma before the sub-query.

Can I only output a single column in a sub-query within SELECT, per your example?
In my case I'm trying to output 6 columns from the sub-query, and one column from the parent/outer query.
The exrates table is a simple table of one row per month.

Does the sub-query actually need to be moved as a JOIN rather than within the SELECT clause?

Cheers, B
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL derived table where clause with value from parent

Post by VladSun »

You are using the inner SELECT in a column "context". That's why it should return only one column.

I think, you don't need nested SELECTs. Just ordinary JOINS.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL derived table where clause with value from parent

Post by batfastad »

Well sub-query in SELECT definitely won't work if I can only get a single column out.
So it would have to be a sub-query somewhere else. I just cannot work this out without doing a sub-query somewhere.

Code: Select all

SELECT FROM_UNIXTIME(`exrates`.`exrate_stamp` , '%Y%m') AS `month`
FROM `exrates` 
INNER JOIN
(SELECT
SUM(CASE WHEN `adbookings`.`currency`='USD' AND `publications`.`sales_category` LIKE '%AMD%' THEN `adinserts`.`rate` ELSE NULL END) AS `amd_usd`,
SUM(CASE WHEN `adbookings`.`currency`='EUR' AND `publications`.`sales_category` LIKE '%AMD%' THEN `adinserts`.`rate` ELSE NULL END) AS `amd_eur`,
SUM(CASE WHEN `adbookings`.`currency`='GBP' AND `publications`.`sales_category` LIKE '%AMD%' THEN `adinserts`.`rate` ELSE NULL END) AS `amd_gbp`,
SUM(CASE WHEN `adbookings`.`currency`='USD' AND `publications`.`sales_category` LIKE '%IDN%' THEN `adinserts`.`rate` ELSE NULL END) AS `idn_usd`,
SUM(CASE WHEN `adbookings`.`currency`='EUR' AND `publications`.`sales_category` LIKE '%IDN%' THEN `adinserts`.`rate` ELSE NULL END) AS `idn_eur`,
SUM(CASE WHEN `adbookings`.`currency`='GBP' AND `publications`.`sales_category` LIKE '%IDN%' THEN `adinserts`.`rate` ELSE NULL END) AS `idn_gbp`
FROM `adinserts`
LEFT JOIN `adbookings` USING(`booking_id`)
LEFT JOIN `invoices` ON `adinserts`.`invoice_id`=`invoices`.`invoice_id`
LEFT JOIN `publications` ON `adinserts`.`publication_id`=`publications`.`publication_id`
WHERE `adbookings`.`booking_stamp`>=1230768000
AND `adbookings`.`booking_stamp`<--THAN CURRENT `exrates`.`exrate_stamp` FROM OUTER SELECT
AND `adinserts`.`rate`>0 AND `adinserts`.`cancel_stamp` IS NULL
AND (`invoices`.`invoice_stamp` IS NULL OR `invoices`.`invoice_stamp`>=--THAN CURRENT `exrates`.`exrate_stamp` FROM OUTER SELECT)
AND `publications`.`sales_category` NOT LIKE '%DW%' AND
`publications`.`sales_category` LIKE '%ISSUE%')
AS `totals` ON ??=??

WHERE `exrates`.`exrate_stamp`>=1230768000
ORDER BY `exrates`.`exrate_stamp`
But I don't know what to join to what, and also how I can get the value from the outer select into my WHERE clause in the sub-query
Post Reply