Joining 3 databases, having same id.

[Nuv]

I have 3 databases having the same structure. However their 'id' which acts as a key start with 1. I would like to join all the databases but my problem is unique key i.e 'id' will become same. For example joining all the 3 databases will result in 3 rows with id='1'. How should i join all the 3 databases and still have unique id. The only way i can think is write a php code and do it with fopen. Is there any other better way ?

 

Structure of the databases :-

 

CREATE TABLE IF NOT EXISTS `funeral` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `street` varchar(200) NOT NULL,
  `city` varchar(100) NOT NULL,
  `shrtstate` varchar(4) NOT NULL,
  `state` varchar(100) NOT NULL,
  `pincode` varchar(15) NOT NULL,
  `phone` varchar(35) NOT NULL,
  PRIMARY KEY (`id`)
)

 

[kickstart]

Hi

 

I am not quite sure what you mean.

 

If you are doing a join between several tables with ID fields then normally the tables would be different. Each would have their own ID but you would bring back the IDs of each table / record. And give each an alias so you can access them.

 

If you mean that you have several databases with the same kind of data (ie, maybe you have a table then an archived table of old records) then you would probably UNION the results together. To tell which row comes from which table you have an extra column with a fixed value. For example

 

SELECT 'MainTable' AS TableName, Id, CusName
FROM MainTable
UNION
SELECT 'ArchiveTable' AS TableName, Id, CusName
FROM ArchiveTable

 

All the best

 

Keith

[Nuv]

I am sorry for using the word 'Join'. Please allow me to elaborate what i am trying to achieve.

 

3 databases like

 

1st database:-

 

(1, 'xxx', '434 OZARK RD', 'Abbeville', 'AL', 'Alabama', '36310', '(334) 585-5261'),
(2, 'xxx', 'PO BOX 8', 'Abbeville', 'AL', 'Alabama', '36310', '(334) 585-5344'),
(3, 'xxx', '434 OZARK RD', 'Abbeville', 'AL', 'Alabama', '36310', '(334) 585-5261'),
(4, 'xxx', 'PO BOX R', 'Collinsville', 'AL', 'Alabama', '35961', '(256) 524-2131'),
(5, 'xxx', '208 Johnson Street', 'Hogansville', 'GA', 'Alabama', '30230', '(706) 637-8623'),

 

2nd database:-

 

(1, 'xxx', 'PO BOX 626', 'Frankfort', 'MI', 'Michigan', '49635', '(231) 352-4121'),
(2, 'xxx', 'PO BOX 146', 'Kaleva', 'MI', 'Michigan', '49645', '(231) 362-3575'),
(3, 'xxx', '305 6TH ST', 'Traverse City', 'MI', 'Michigan', '49684', '(231) 947-6347'),
(4, 'xxx', '6651 W HOUGHTON LAKE DR', 'Houghton Lake', 'MI', 'Michigan', '48629', '(517) 422-5711'),

 

3rd database:-

 

(1, 'xxx', 'PO BOX 413', 'Shelburne', 'VT', 'Vermont', '05482', '(802) 985-3370'),
(2, 'xxx', '30 SCHOOL ST', 'Montpelier', 'VT', 'Vermont', '05602', '(802) 223-2751'),
(3, 'xxx', '68 PINECREST DR', 'Essex Junction', 'VT', 'Vermont', '05452', '(802) 879-9477'),
(4, 'xxx', '1077 N ZAC ST', 'Lisbon', 'NH', 'Vermont', '03585', '(603) 444-3311'),

 

These databases have different states so i would like to add all these so that i have just 1 database with different stated instead of 3, so that my final database becomes

 

(1, 'xxx', '434 OZARK RD', 'Abbeville', 'AL', 'Alabama', '36310', '(334) 585-5261'),
(2, 'xxx', 'PO BOX 8', 'Abbeville', 'AL', 'Alabama', '36310', '(334) 585-5344'),
(3, 'xxx', '434 OZARK RD', 'Abbeville', 'AL', 'Alabama', '36310', '(334) 585-5261'),
(4, 'xxx', 'PO BOX R', 'Collinsville', 'AL', 'Alabama', '35961', '(256) 524-2131'),
(5, 'xxx', '208 Johnson Street', 'Hogansville', 'GA', 'Alabama', '30230', '(706) 637-8623'),
(1, 'xxx', 'PO BOX 626', 'Frankfort', 'MI', 'Michigan', '49635', '(231) 352-4121'),
(2, 'xxx', 'PO BOX 146', 'Kaleva', 'MI', 'Michigan', '49645', '(231) 362-3575'),
(3, 'xxx', '305 6TH ST', 'Traverse City', 'MI', 'Michigan', '49684', '(231) 947-6347'),
(4, 'xxx', '6651 W HOUGHTON LAKE DR', 'Houghton Lake', 'MI', 'Michigan', '48629', '(517) 422-5711'),
(1, 'xxx', 'PO BOX 413', 'Shelburne', 'VT', 'Vermont', '05482', '(802) 985-3370'),
(2, 'xxx', '30 SCHOOL ST', 'Montpelier', 'VT', 'Vermont', '05602', '(802) 223-2751'),
(3, 'xxx', '68 PINECREST DR', 'Essex Junction', 'VT', 'Vermont', '05452', '(802) 879-9477'),
(4, 'xxx', '1077 N ZAC ST', 'Lisbon', 'NH', 'Vermont', '03585', '(603) 444-3311'),

 

I can just cut and paste it but as you can notice my id's become same.How can i have different id's ?

 

 

 

 

[kickstart]

Hi

 

You can't really have a different id as that id refers to the table record.

 

What you can do is the 2nd suggestion I made. Doing a UNION and having an extra column that specifies the table that row came from.

 

If you want a unique row number just for display purposes then you can use counter variables. Something like this:-

 

SET @rownum = 0;
SELECT @rownum:=@rownum+1,'State1', Id, Town
FROM state1
UNION
SELECT @rownum:=@rownum+1,'State2', Id, Town
FROM state2

 

However to reference the original rows (such as to do any updates) you will still need the original Id and the table it is from.

 

All the best

 

Keith