PHP MySQL Select query

Question

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 the 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";

you could also consider more explicit/consistent column names, like user_id instead of just store in the retail-table. store_id would also work, but since (if I understand it correctly), the stores are in the users table, user_id would be more consistent — Flambino
– Flambino, Commented Jul 31, 2011 at 21:04

Jacob · Accepted Answer · 2011-07-31 21:00:57Z

2

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.

answered Jul 31, 2011 at 21:00

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JK. · Accepted Answer · 2011-07-31 21:01:25Z

0

This will omit the store id from your result set:

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

answered Jul 31, 2011 at 21:01

JK.

5,1261 gold badge29 silver badges26 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Select query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related