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;

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

      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.

        Thanks I will recode this without it.

          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'.

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

              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.

                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;

                  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.

                    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🙂

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

                        Write a Reply...