MySql INNER JOIN, ERROR #1066 [duplicate]

Question

I have 3 database.

grameenphone_bill » Table: tbl_admin--> cp_id

banglalink_bill »Table: tbl_billing_response-> cpid

robi_bill »Table: tbl_billing_response-> cpid

All the three db's table's have two fields common i.e ChargedAmount and cpid. The login is done through grameenphone_bill » Table: tbl_admin.

Now, i want to calculate the revenue i.e ChargedAmount by using cpid

I have written a query but getting error #1066 - Not unique table/alias: 'tbl_admin'

here's the query i wrote.

SELECT grameenphone_bill.tbl_admin.cp_id,
       grameenphone_bill.tbl_admin.cp_name,
       banglalink_bill.tbl_billing_response.cpid,
       robi_bill.tbl_billing_response.cpid,
       grameenphone_bill.tbl_billing_response.ChargedAmount,
       banglalink_bill.tbl_billing_response.ChargedAmount,
       robi_bill.tbl_billing_response.ChargedAmount
FROM grameenphone_bill.tbl_admin
INNER JOIN grameenphone_bill.tbl_admin ON grameenphone_bill.tbl_admin.cp_id=banglalink_bill.tbl_billing_response.cpid
INNER JOIN banglalink_bill.tbl_billing_response ON banglalink_bill.tbl_billing_response.cpid=robi_bill.tbl_billing_response.cpid

Please tell me, where i'm going wrong. Thanks!!

Just use alias name for joining your problem will resolve.

Manoj Sharma
– Manoj Sharma

2014-12-12 09:32:41 +00:00
Commented Dec 12, 2014 at 9:32 — Manoj Sharma
– Manoj Sharma, Commented Dec 12, 2014 at 9:32

Mureinik · Accepted Answer · 2014-12-12 09:42:57Z

1

You join is incorrect - you don't need to join tbl_admin, it's already in your from clause:

SELECT     grameenphone_bill.tbl_admin.cp_id,
           grameenphone_bill.tbl_admin.cp_name,
           banglalink_bill.tbl_billing_response.cpid,
           robi_bill.tbl_billing_response.cpid,
           grameenphone_bill.tbl_billing_response.ChargedAmount,
           banglalink_bill.tbl_billing_response.ChargedAmount,
           robi_bill.tbl_billing_response.ChargedAmount
FROM       grameenphone_bill.tbl_admin
INNER JOIN banglalink_bill.tbl_billing_response ON 
           grameenphone_bill.tbl_admin.cp_id = 
           banglalink_bill.tbl_billing_response.cpid
INNER JOIN robi_bill.tbl_billing_response ON 
           banglalink_bill.tbl_billing_response.cpid = 
           robi_bill.tbl_billing_response.cpid

edited Dec 12, 2014 at 9:42

answered Dec 12, 2014 at 9:33

Mureinik

316k54 gold badges401 silver badges405 bronze badges

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

2 Comments

Constantinez Over a year ago

Getting error: #1054 - Unknown column 'robi_bill.tbl_billing_response.cpid' in 'field list'

Mureinik Over a year ago

arg, dropped a line there... fixed - see my latest edit

Venkata Krishna · Accepted Answer · 2014-12-12 09:35:08Z

You are joining grameenphone_bill.tbl_admin with grameenphone_bill.tbl_admin itself. I think it should have been robi_bill.tbl_billing_response instead.

SELECT grameenphone_bill.tbl_admin.cp_id,
       grameenphone_bill.tbl_admin.cp_name,
       banglalink_bill.tbl_billing_response.cpid,
       robi_bill.tbl_billing_response.cpid,
       grameenphone_bill.tbl_billing_response.ChargedAmount,
       banglalink_bill.tbl_billing_response.ChargedAmount,
       robi_bill.tbl_billing_response.ChargedAmount
FROM grameenphone_bill.tbl_admin
INNER JOIN banglalink_bill.tbl_billing_response ON grameenphone_bill.tbl_admin.cp_id=banglalink_bill.tbl_billing_response.cpid
INNER JOIN robi_bill.tbl_billing_response ON banglalink_bill.tbl_billing_response.cpid=robi_bill.tbl_billing_response.cpid

Hope this helps.

Manoj Sharma · Accepted Answer · 2014-12-12 09:38:24Z

0

When you are selecting one table two times in one select you should give an alias to at least one of it :

SELECT gta.cp_id,
       gta.cp_name,
       ba.cpid,
       rb.cpid,
       gta.ChargedAmount,
       ba.ChargedAmount,
       rb.ChargedAmount
FROM grameenphone_bill.tbl_admin gta
INNER JOIN banglalink_bill.tbl_billing_response ba ON gta.cp_id=ba.cpid
INNER JOIN robi_bill.tbl_billing_response rb ON ba.cpid=rb.cpid

I hope your problem will resolve.

answered Dec 12, 2014 at 9:38

community wiki

Manoj Sharma

Collectives™ on Stack Overflow

MySql INNER JOIN, ERROR #1066 [duplicate]

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related