Error Code: 1066. Not unique table/alias: 'zones', Joining 3 database tables with foreign keys

Question

My database tables

zones [ id zone zone_manager_email]

cities [ id city zone_id ]

store[id name store_no city_id]

I have these 3 tables, I tried to inner join them , but my code is not working.

 SELECT zones.zone, zones.zone_manager_email,store.store_id
 FROM zones,store
 INNER JOIN zones on zones.id = cities.zone_id
 INNER JOIN store ON store.city_id = city.id

Why are you joining each table twice? – Salman A Dec 17 '18 at 11:46 — Salman A, Dec 17 '18 at 11:46

score 0 · Accepted Answer · answered Dec 17 '18 at 11:47

0

First you never really reference the cities table, then you seem a bit consfused over the difference between different ways of doing this.

from a, b WHERE a.col1 = b.col1 vs from a inner join b on a.cool1 = b.col1.

Try this one :).

SELECT zones.zone, zones.zone_manager_email,store.store_id
FROM zones
INNER JOIN cities ON zones.id = cities.zone_id
INNER JOIN store ON store.city_id = city.id

answered Dec 17 '18 at 11:47

user1694674

101
5

Error Code: 1054. Unknown column 'city.id' in 'on clause' now its shows like this – Vijith Vs Dec 17 '18 at 11:59
I didn't proofread your code enough. Try to figure it out by yourself and if it doesn't work switch from singular form to plural form on city :) – user1694674 Dec 17 '18 at 12:05
thank you, it was my mistake , it works now. – Vijith Vs Dec 17 '18 at 12:16

Error Code: 1066. Not unique table/alias: 'zones', Joining 3 database tables with foreign keys

1 Answers1