PHP MySQL Select query

I have these two tables setup and the below query running. The problem is I need to be able to output both columns called store at the same time if I can from th开发者_如何学编程e same query. Also at the moment if I try to output the column id it outputs the column id from the users table and not the retail table as I would like.

I believe there is a way to do this, but if I am incorrect please do tell me, otherwise I will be knocking my head against a brick wall for much longer.

Cheers for any help.

Table users

id

store (this is the name of the store)

Table retail

id

store (this refers to the id in the users field)

$query ="SELECT * FROM retail JOIN users ON users.id=retail.store";

---

SELECT users.id, users.store, retail.id, retail.store
FROM retail JOIN users ON users.id=retail.store

You have to prepend the table's name when referencing ambiguous column names.

---

This will omit the store id from your result set:

SELECT retail.id, users.store
FROM retail 
JOIN users ON users.id = retail.store