Understanding subqueries

[catchzz]

I am not all too good at complex MySQL queries and this is one that I have been struggling to find the answer to for some time now.  Basically what I want to do is view a table based on the data stored in an existing MySQL table.

 

Example:

 

table Sales

 

Product  |  Region  |  Quantity  |  Dollars  |

-----------------------------------------------------------

    A              US                  10              45

    B              US                  12              52

    B              MX                  6              27

    B              US                  11              48

 

From here what I need is a query that would give me the following output:

 

Product  |  US Quantity  |  US Dollars  |  MX Quantity  |  MX Dollars  |

---------------------------------------------------------------------------------------------

    A              10                        45                      0                        0

    B              23                        100                    6                        27

 

How would I go about creating this query to get that bottom table from an already existing table similar to my top example?

 

I appreciate if someone can alleviate my frusteration!

[optikalefx]

SELECT Sales.*, otherTable.USQuantity, otherTable.USDollars, otherTable.MXQuantity, otherTable.MXDollars

FROM Sales

LEFT JOIN otherTable ON (Sales.Product = otherTable.Product)

 

basically your using the Product field on both tables to JOIN those 2 tables together. Left join means i dont care if the join results in null values, just give me the row no matter what. JOIN alone (without the left) would do the same but not give you the row if the key did not exist on the other table.