正确使用MySQL JOIN

Question

I've never used JOIN before, so I'm having trouble how to properly make the MySQL query using PHP.

I have a table named store with rows itemid, prices, etc. I also have a table named item_list with rows id, attributes, etc. .

I want to be able to access 'store' itemid's and using those, access item_list id's and the that id's attributes. I came up with a query like this:

$query = "SELECT store.price, item_list.*  
            FROM store, item_list 
           WHERE item_list.id = store.itemid";

I get an error when I try to execute the query. Any suggestions?

Answer 1

$query = "SELECT store.price, item_list.*  
            FROM store LEFT JOIN item_list ON item_list.id = store.itemid

Also you should be explicit about which columns you are selecting, rather than the *. This is for safety and performance.

Another nice feature is table aliases, which make your SQL a little less verbose:

$query = "SELECT s.price, il.col1, il.col2, il.col3  
            FROM store s LEFT JOIN item_list il ON s.itemid = il.id