[RESOLVED] #1066 - Not unique table/alias

[Simplyput]

I have re-coded this a 1000 times and it keeps giving me the same error code. Can someone please look at this and give me some suggestions on what I might be doing wrong? I will be very thankful.

SELECT ac.cms_active_starter.*, di.cms_dig_starter.*, ip.cms_ips_starter.*, pa.cms_parkeon_starter.*, pb.cms_paybyphone_starter.* FROM cms_active_starter ac, cms_dig_starter di, cms_ips_starter ip, cms_parkeon_starter pa, cms_pbp_starter pb
JOIN cms_dig_starter di
 ON di.date = ip.transaction_date 
    JOIN cms_parkeon_starter pa
        ON pa.terminal_date = di.date
    JOIN cms_active_starter ac 
        ON ac.paybyphone_number = pb.verrus_lot_id
		JOIN cms_ips_starter ip
				ON ip.amount = pa.amount
WHERE ac.old_meter_number = ip.pole limit 0,30; 

[Weedpacket]

If you specify tables that you're joining on in the JOIN clause, you shouldn't specify them in the FROM clause as well.

[NogDog]

Looks as if you want something like this (plus I'm a big proponent of newlines and consistent indenting of SQL):

SELECT 
  ac.*,
  di.*,
  ip.*, 
  pa.*, 
  pb.* 
FROM cms_active_starter ac
  JOIN cms_dig_starter di ON di.date = ip.transaction_date 
  JOIN cms_parkeon_starter pa ON pa.terminal_date = di.date
  JOIN cms_active_starter ac  ON ac.paybyphone_number = pb.verrus_lot_id
  JOIN cms_ips_starter ip ON ip.amount = pa.amount
WHERE ac.old_meter_number = ip.pole limit 0, 30;

In fact, since you apparently want every column of every table, you could replace all those <table_alias>.* instances in the SELECT clause with just a single "*" character.

[Simplyput]

Thanks I will recode this without it.

[Simplyput]

bump I did this post in error.

[Simplyput]

NogDog;11052047 wrote:

Looks as if you want something like this (plus I'm a big proponent of newlines and consistent indenting of SQL):

SELECT 
  ac.*,
  di.*,
  ip.*, 
  pa.*, 
  pb.* 
FROM cms_active_starter ac
  JOIN cms_dig_starter di ON di.date = ip.transaction_date 
  JOIN cms_parkeon_starter pa ON pa.terminal_date = di.date
  JOIN cms_active_starter ac  ON ac.paybyphone_number = pb.verrus_lot_id
  JOIN cms_ips_starter ip ON ip.amount = pa.amount
WHERE ac.old_meter_number = ip.pole limit 0, 30;

In fact, since you apparently want every column of every table, you could replace all those <table_alias>.* instances in the SELECT clause with just a single "*" character.

I swear I coded it almost like you did and got an error so I took your code untouched and ran it in phpmyadmin and it gave me an error. #1066 - Not unique table/alias: 'ac'.

[Weedpacket]

Count your uses of the 'ac' alias.

[NogDog]

I'm just guessing that maybe "JOIN cms_active_starter ac ..." should be "JOIN <some other table?> pb ..."

[sneakyimp]

If you use a table name twice in your query with aliases as you have, you need to give it a different alias each time you reference it.

[Simplyput]

NogDog;11052069 wrote:

I'm just guessing that maybe "JOIN cms_active_starter ac ..." should be "JOIN <some other table?> pb ..."

I tried suggestion and now I have #1054 - Unknown column 'ip.transaction_date' in 'on clause' .

SELECT 
  ac.*,
  di.*,
  ip.*, 
  pa.*, 
  pb.* 
FROM cms_active_starter ac
  JOIN cms_dig_starter di ON di.date = ip.transaction_date 
  JOIN cms_parkeon_starter pa ON pa.terminal_date = di.date
  JOIN cms_pbp_starter pb  ON pb.verrus_lot_id = ac.paybyphone_number
  JOIN cms_ips_starter ip ON ip.amount = pa.amount  

WHERE ac.old_meter_number = ip.pole limit 0, 30;

[Derokorian]

You can't use an alias until you define it. Notice you're doing a join on cms_dig_starter_di ON ip.transaction_date but this join is BEFORE the join that defines the ip alias.

[Simplyput]

Derokorian;11052097 wrote:

You can't use an alias until you define it. Notice you're doing a join on cms_dig_starter_di ON ip.transaction_date but this join is BEFORE the join that defines the ip alias.

Okay thanks for this info. Now if I define it in the from clause cms_ips_starter ip I still get the same error code. How would I define it before the on clause If I already defined another table before the on clause. Every example I see follows this code logic and works. Maybe their just bad code examples I guess🙂

[sneakyimp]

How about you start with a simple query that works and then add one join at a time?