group by - Creating MYSQL column (Alias) based on the value of that column -

group by - Creating MYSQL column (Alias) based on the value of that column -

i have next tables

products, prices, stores (suppliers) , country

products table:

productid, productname

stores table:

storeid, storename, country (country name of supplier)

price table:

priceid, date, price, storeid, productid

country table

countryid, storeid, countryname

sample cost table info (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 need create line based chart. there 2 lines on chart. create 2 lines need 2 columns table - uae , uk

so expected result

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 confused this... trying 2 table view (one stores.countryid = 'ae' , 1 stores.countryid <> 'ae').

please help.

mysql group-by substring column-alias