Write a query to fetch data from two database

Question

Followings are the table name.both database have same table and column name.

field_data_field_name                                   
|  Entity_id| field_name_value|  
|    1      |       XYZ       |

field_data_field_address
-----------------------------------
|  Entity_id | field_address_value|  
|    1       |     abc            |

field_data_field_county
----------------------------------
|  Entity_id | field_county_value|  
|    1       | mumbai            |

field_data_field_select_area
---------------------------------------
|  Entity_id | field_select_area_value|  
|    1       | pension                |

users
------------------------------------
|  uid  | mail                     |  
|    1  | sopu.phadke080@gmail.com |

In single database on basis of Entity_id=uid we join tables and data fetch successfully.

following is query for single database.

SELECT 
field_data_field_name.field_name_value,
field_data_field_address.field_address_value,
field_data_field_county.field_county_value,
field_data_field_select_area.field_select_area_value,
users.mail
FROM users 
INNER JOIN field_data_field_name On field_data_field_name.entity_id= uid
INNER JOIN field_data_field_address On field_data_field_address.entity_id = uid
INNER JOIN field_data_field_county On field_data_field_county.entity_id = uid
INNER JOIN field_data_field_select_area On field_data_field_select_area.entity_id = uid

I create this query but not working.please help me.

SELECT 
a.field_name_value,
b.field_address_value, 
c.field_county_value,
d.field_select_area_value,
e.field_name_value,
f.field_address_value,
g.field_county_value,
h.field_select_area_value
FROM 
dbeng.field_data_field_name As a,
dbeng.field_data_field_address As b,
dbeng.field_data_field_county As c,
dbeng.field_data_field_select_area As d,
inner join dbspa.field_data_field_name As e ON <field_data_field_name.field_name_value>=<field_data_field_name.field_name_value>,
inner join dbspa.field_data_field_address As f ON <field_data_field_address.field_address_value>=<field_data_field_address.field_address_value>,
inner join dbspa.field_data_field_county As g ON <field_data_field_county.field_county_value>=<field_data_field_county.field_county_value>,
inner join dbspa.field_data_field_select_area As h ON <field_data_field_select_area.field_select_area_value>=<field_data_field_select_area.field_select_area_value>
WHERE d.field_select_area_value ='Pensión Compensatoria' && h.field_select_area_value ='Pensión Compensatoria' && c.field_county_value ='Alameda' && g.field_county_value='Alameda'

If the two tables have the same Table and Column values, then just integrate one database into the other, it's a waste of space having two doing the same thing. — Paulie, Nov 12 '14 at 09:29
actually i create a sub domain for english and spanish. so drupal create two different database. — Rahul Varma, Nov 12 '14 at 09:31
http://www.databasejournal.com/features/mysql/article.php/3835506/Fetching-Data-from-Multiple-Tables-using-Joins.htm — Paulie, Nov 12 '14 at 09:43
I have rolled your question back to a previous version. Questions on stackoverflow should also help other users, so it does not help to change your post to contain the answer instead of the question. — Peter Lang, Nov 13 '14 at 12:36

Peter Lang · Accepted Answer · 2014-11-12T11:40:39.730

0

The concept of storing such information in two different databases does not sound right, but if you need to get data from both, you will have to do a UNION ALL:

SELECT x.col1, x.col2
FROM
(
    SELECT a.col1, b.col1
    FROM db1.a
    JOIN db1.b ON ( ... )
  UNION ALL
    SELECT a.col1, b.col1
    FROM db2.a
    JOIN db2.b ON ( ... )
) x
WHERE x.col1 = ...

edited Nov 12 '14 at 11:40

answered Nov 12 '14 at 10:24

Peter Lang

52,486
27
146
157

kindly please explain me what is x.col1 and x.col2 – Rahul Varma Nov 13 '14 at 08:48
`x` is the alias of my sub-query, `col1` and `col2` are columns of my tables (`field_name_value` and `field_county_value` in your case). – Peter Lang Nov 13 '14 at 09:09
i have total 10 tables so this is write. x.field_name_value x.field_county_value x.mail etc.bcoz 10 values comes from 10 tables – Rahul Varma Nov 13 '14 at 09:12
HI peter i m getting this error "#1248 - Every derived table must have its own alias" – Rahul Varma Nov 13 '14 at 09:23
That's what I solved by using the alias `x`. See http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias – Peter Lang Nov 13 '14 at 09:31

score 0 · Answer 2 · answered Nov 12 '14 at 11:15

0

select Col1,Col2 from DB1..Tbl1 join Tbl2 on Tbl2.Col2 = DB1..Tbl1.Col1

You can access table of another database with syntax as : DatabaseName..TableName.ColumnName

answered Nov 12 '14 at 11:15

Rohit Shirgaonkar

11
1

Write a query to fetch data from two database

2 Answers2