I am not sure I set this up correctly, but tried to plan out how to setup my tables in the most efficient manner for future compatibility within the system I am building. Here is what I have.
I have 3 tables as follows:
Accounts
Locations
Users
Each table is joined by only 1 common field as follows:
Accounts has a_id (for account_id)
Locations has l_id (for location_id) AND a_id
Users has u_id (for user_id) AND l_id
I am trying to pull information (in this case, the names) from each of the above tables by only knowing the u_id of a user.
The following is an example SELECT statement that I am trying to use:
SELECT u.u_f_name, u.u_l_name, l.l_name, a.a_name FROM users u, locations l, accounts a WHERE u_id = 2 AND l.l_id = u.l_id AND a_id = l.a_id
When I attempt to run this query in phpMyAdmin, I receive the following error.
Column: 'a_id' in where clause is ambiguous
Can anyone tell me a more efficient select statement that I could use in place of the one above so that I can pull the necessary data with only a user id (u_id) known.
Thanks in advance.
