CODEWITHSUNDEEP
phpmysql
Alper
Alper

Reputation: 107

Proper use of MySQL JOIN

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?

Upvotes: 1

Views: 111

Answers (3)

Cybercartel
Cybercartel

Reputation: 12592

If you wanz zo exclude all rows in both tables with NULL values then you want to use a INNER JOIN. A LEFT JOIN exclude only rows with NULL values on the left side of the join.

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

Upvotes: 0

Ash Eldritch
Ash Eldritch

Reputation: 1514

$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

Upvotes: 2

Esselans
Esselans

Reputation: 1546

You've a typo. It should be 

WHERE item_list.id = store.itemid

you have 

WHERE item.id =

Upvotes: 0

Related Questions