CODEWITHSUNDEEP
mysql
Noob Coder
Noob Coder

Reputation: 2917

MySQL join select on two different database tables

Beginner here. I want to join two tables that resides in two different databases. Let's say:

Database 1 name is: a_database
Database 2 name is: b_database

a_database table name is: coupons and columns are: id|coupon|created_at b_database table name is: partner_company_clients and columns are: id|phone|coupon_id|created_at

The query is: 

SELECT * 
FROM b_database.partner_company_clients pcc
INNER JOIN (SELECT id, coupon from a_database.coupons) mac on 
mac.id=pcc.coupon_id
WHERE pcc.partner_company_id=1 
AND pcc.deleted_at IS NOT NULL;

This works perfectly fine. But I do not want to pull everything from b_database.partner_company_clients table. Only the phone number column. If I remove the * and specify the column names it throws an error. I can't say why.

The select query becomes: 

SELECT id, phone 
    FROM b_database.partner_company_clients pcc
    INNER JOIN (SELECT id, coupon from a_database.coupons) mac on 
    mac.id=pcc.coupon_id
    WHERE pcc.partner_company_id=1 
    AND pcc.deleted_at IS NOT NULL;

Error:

Error: #1052 - Column 'id' in field list is ambiguous

Upvotes: 3

Views: 4742

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

Your query involves 2 id columns one from inner query and second from partner_company_clients so using select id it isn't clear enough which id column you want so use alias before the column name or use complete db and table name before columns 

SELECT mac.id, pcc.phone 
....

or 

SELECT mac.id, b_database.partner_company_clients.phone 
....

Upvotes: 2

Related Questions