I have a table with the following structure:
id, Metal, AmPm, GBP, USD, EUR, Updated, TimeStamp
Sample values include:
INSERT INTO `LondonFixes` (`id`, `Metal`, `AmPm`, `GBP`, `USD`, `EUR`, `Updated`, `TimeStamp`) VALUES
(228, 'Gold', 'AM', '1779.22000', '2748.00000', '2253.20000', '2012-07-13', '2012-07-13 15:19:35'),
(224, 'Gold', 'PM', '1022.33700', '1579.00000', '1294.05000', '2012-07-13', '2012-07-13 13:16:59'),
I have queried the data and am able to get the last 10 rows and order them how I want as follows:
$r = mysql_query("SELECT * FROM(SELECT id, GBP, AmPm, Updated FROM LondonFixes WHERE Metal = 'Gold' AND UPDATED < now() ORDER BY id DESC LIMIT 10) AS src ORDER BY id ASC");
This returns a table of the form:
id, GBP, AmPm, Updated
The problem is that I need a table of the form:
id, GBP_AM, GBP_PM, Updated
Basically, AmPm values can either contain the values "AM" or "PM". I want to show the GBP value for both AM and PM on one line where updated is the same on both lines.
e.g. Required output based on above sample data:
1, 1779.22000, 1022.33700, 2012-07-13
I think that this is a self Join, but I just don't understand self joins and I am not even sure that Self Joins is what I need.