0

I have the following tables

Products, Prices, stores (Suppliers) and Country

Products table:

ProductID, ProductName

Stores Table:

StoreID, StoreName, Country (country name of the supplier)

Price Table:

PriceID, Date, Price, StoreID, ProductID

Country Table

CountryID, StoreID, CountryName

Sample Price Table Data (ID, Date, Price, StoreID, ProductID)

1, 01-10-2014, 1500, 1, 8
2, 01-10-2014, 1800, 2, 9
3, 10-10-2014, 1700, 2, 8
4, 01-09-2014, 1200, 1, 8
5, 07-09-2014, 1300, 2, 8

In Stores Table

1, Supplier1, UAE
2, Supplier2, GB

In Product Table

8, iPad Air 32GB 4G
9, iPad Air WiFi 16GB

Now i need to create a line based chart. There will be two lines on the chart. To create the two lines i need two columns from this table - the UAE and UK

So my expected result is 

ID Date        UAEPrice  UKSupplier SupplierName
1 01-10-2014  1500       Null       Supplier-1
2 01-10-2014  NULL       1800       Supplier-2
3 10-10-2014  NULL       1700       Supplier-2
4 01-09-2014  1200       Null       Supplier-1
5 07-09-2014 Null        1300       Supplier-1

I am confused with this... Currently I am trying with two Table View (one with stores.countryID = 'AE' and one with stores.CountryID <> 'AE').

Please help.

Riyas Rawther
  • 21
  • 2
  • Can you post your current query? – AdamMc331 Oct 29 '14 at 16:36
  • I got some similar question http://stackoverflow.com/questions/10925445/mysql-select-dynamic-row-values-as-column-names-another-column-as-value I think we should use GROUP_CONCAT() anyone can answer with GROUP_CONCAT() ? – Riyas Rawther Oct 29 '14 at 16:55
  • I almost done... If i ca show the Date one the result then i will get a result... http://sqlfiddle.com/#!2/aced91/2 – Riyas Rawther Oct 29 '14 at 17:18
  • So is the question how to input the date column? You seem to have answered your own question for how to input a column alias. – AdamMc331 Oct 29 '14 at 17:23
  • yes... I got the answer from http://stackoverflow.com/questions/10925445/mysql-select-dynamic-row-values-as-column-names-another-column-as-value thanks all... – Riyas Rawther Oct 30 '14 at 03:06

0 Answers0