Not having to join in 135 tables

[cooldude832]

I have 4 tables (users is only used for the UserID FK)

 

Purchase

CREATE TABLE `purchases` (
  `PurchaseID` bigint(20) NOT NULL auto_increment,
  `UserId` bigint(20) NOT NULL,
  `CurrencyID` bigint(20) NOT NULL,
  `PurchaseDate` datetime NOT NULL,
  `Amount` float NOT NULL,
  `Notes` text collate utf8_unicode_ci NOT NULL,
  `Sold` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`PurchaseID`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

 

Currencies

CREATE TABLE `currencies` (
  `CurrencyID` bigint(20) NOT NULL auto_increment,
  `Name` varchar(128) collate utf8_unicode_ci NOT NULL,
  `Abbreviation` varchar(5) collate utf8_unicode_ci NOT NULL,
  `Active` tinyint(1) NOT NULL default '1',
  `Metal` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`CurrencyID`)
) ENGINE=MyISAM AUTO_INCREMENT=154 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=154 ;

 

currencies_value

CREATE TABLE `currencies_values` (
  `PriceID` bigint(20) NOT NULL auto_increment,
  `PriceTime` datetime NOT NULL,
  `CurrencyID` bigint(20) NOT NULL,
  `Price` float NOT NULL,
  `BatchID` bigint(20) NOT NULL,
  PRIMARY KEY  (`PriceID`),
  KEY `CurrencyID` (`CurrencyID`)
) ENGINE=MyISAM AUTO_INCREMENT=24013 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=0 ;

 

I want to get the sum of each individual currencies in purchases for a specific user id where Sold != '1' and the value of each individual currency (from the last batch run of getting prices)

 

My issue is the way I would know how to do it is

		SELECT
			SUM(C1.Amount) as C1V,
			SUM(C2.Amount) as C2V,
			SUM(C3.Amount) as C3V,
			V1.Price as V1P,
			V2.Price as V2P,
			V3.Price as V3P

		FROM `purchases`

		LEFT JOIN `purchases` as C1 on (C1.UserID = 'USERIDHERE' and C1.CurrencyID = '1' and Sold = '0')
		LEFT JOIN `purchases` as C2 on (C2.UserID = 'USERIDHERE' and C2.CurrencyID = '2' and Sold = '0')
		LEFT JOIN `purchases` as C3 on (C3.UserID = 'USERIDHERE' and C3.CurrencyID = '3' and Sold = '0')

		LEFT JOIN `currencies_values` as V1 on (V1.CurrencyID = '1' and MAX(V1.BatchID))
		LEFT JOIN `currencies_values` as V2 on (V2.CurrencyID = '2' and MAX(V2.BatchID))
		LEFT JOIN `currencies_values` as V3 on (V3.CurrencyID = '3' and MAX(V3.BatchID))

 

Well I have 153 currencies and I run this query on every page load so I see a problem doing that

 

Any ideas?

[cooldude832]

well I rewrote it a bit but my issue is the sum portion (I cut it down because it repeats for 154 times but ti worked fine 0.020 second avg and explian looked good)

 

SELECT
		GROUP_CONCAT(currencies_values.Price ORDER BY currencies_values.CurrencyID) as Prices,

			SUM(
			IF(purchases.CurrencyID = '1' and purchases.Sold = '0' and purchases.UserID = '1',purchases.Amount , 0)) as V1,
			SUM(
			IF(purchases.CurrencyID = '2' and purchases.Sold = '0' and purchases.UserID = '1',purchases.Amount , 0)) as V2,
			SUM(
			IF(purchases.CurrencyID = '3' and purchases.Sold = '0' and purchases.UserID = '1',purchases.Amount , 0)) as V3,
			SUM(
			IF(purchases.CurrencyID = '4' and purchases.Sold = '0' and purchases.UserID = '1',purchases.Amount , 0)) as V4,
			SUM(
			IF(purchases.CurrencyID = '5' and purchases.Sold = '0' and purchases.UserID = '1',purchases.Amount , 0)) as V5,
			SUM(
			IF(purchases.CurrencyID = '6' and purchases.Sold = '0' and purchases.UserID = '1',purchases.Amount , 0)) as V6,
			SUM(
			IF(purchases.CurrencyID = '7' and purchases.Sold = '0' and purchases.UserID = '1',purchases.Amount , 0)) as V7,
			SUM(
			IF(purchases.CurrencyID = '8' and purchases.Sold = '0' and purchases.UserID = '1',purchases.Amount , 0)) as V8,
			SUM(
			IF(purchases.CurrencyID = '9' and purchases.Sold = '0' and purchases.UserID = '1',purchases.Amount , 0)) as V9,
			SUM(
			IF(purchases.CurrencyID = '10' and purchases.Sold = '0' and purchases.UserID = '1',purchases.Amount , 0)) as V10
		FROM
			`currencies_values`
		LEFT JOIN `purchases` ON(UserID = '1' and Sold = '0')
		WHERE
			BatchID = '176'

 

When the Rows are populated in the purchases table for that "currencyID" the thing seems to sum the value as number of rows matching times the amount so 1 match = 154 2 = 308 etc.

 

Any ideas?

[fenway]

I want to get the sum of each individual currencies in purchases for a specific user id where Sold != '1' and the value of each individual currency (from the last batch run of getting prices)

Slow down there.

 

One problem at a time.

 

"sum of each individual currencies in purchases for a specific user id  where Sold != '1'  " == simple SUM() with a GROUP BY on currencyID with a where on user_id = <whatever> and sold != 1

 

Get that working first... that should be quite simple.

[cooldude832]

Okay this looks semi good but now 2 semi issues

SELECT 
currencies_values.Price AS Prices,
SUM( purchases.Amount ) AS Amounts,
currencies_values.CurrencyID AS CurrencyID

FROM `purchases`

LEFT JOIN `currencies_values` ON ( currencies_values.CurrencyID = purchases.CurrencyID AND BatchID = '185' )

WHERE UserID = '1'

GROUP BY purchases.CurrencyID

Prices   	  Amounts   	  CurrencyID   	  Name
0.001205 	250 	23 	Canadian Dollar
1 	1 	53 	Gold Ounces
1.5473 	560 	112 	Platinum Ounces

1) Can I join the currencies_values table on the MAX(BatchID)  (I tried this and get a group by issue)

2) Can I get 1 row that has just an Amount which is the sum of each rows' Amount*Price

 

So example in my rows the first row retrieved says I have 250 Canadian dollars and each one has an assessed value of 0.001205 (its how much gold in ounces a dollar Canadian buys, thus why gold is 1)

So I want to get 1 number back if possible that is equal to:

 

(0.001205*250)+(1*1)+(1.5473*560)

 

I don't need the individual amounts or values or names/ids in this query just the single "number"  I can in php just do while($row...) and make it sum but I think MySQL should be able to do this

Make sense? 

[fenway]

You're skipping steps again.... in order for your query to be robust, you ALWAYS have to use a batch id... otherwise it doesn't make sense to select a non-group by'ed column, like price.

 

Isn't the max batch id going to be different for each currency?

 

And yes, once you have this table, you should be able to sum(c1*c2) in mysql with the above query as a derived table.

[cooldude832]

well BatchID is the cron job's run # and it stores how long it took to run the cron job  (so 185 was my last run of the cron job)

 

I'm still a bit confused I did the submation in the SUM() but I will still have between 0 and 153 rows when I want 1 row  (The batchid Max isn't super important because I realized if the cron is running some items might go foobar)

SELECT 
currencies_values.Price AS Prices,
SUM( purchases.Amount ) AS Amounts,
currencies_values.CurrencyID AS CurrencyID,
currencies.Name as Name,
SUM(purchases.Amount*currencies_values.Price) as Value

FROM `purchases`

LEFT JOIN `currencies_values` ON ( currencies_values.CurrencyID = purchases.CurrencyID AND BatchID = '185' )
LEFT JOIN `currencies` ON(currencies.CurrencyID = purchases.CurrencyID)
WHERE UserID = '1'

GROUP BY purchases.CurrencyID, BatchID

[fenway]

Now I don't know what you're doing... I don't see you using a derived table at all.

[cooldude832]

Yeah I think I need to sit down and do it for real and no on my PDA I'll try it later but I think I'm close

[fenway]

You were close... but then you omitted the derived table and just tried to keep joining.