Habib Rehman Habib Rehman - 1 year ago 56
MySQL Question

Insert one db's different tables data to other db's single table

i'm running into a complex problem, the data in a db has three tables.


-- default_users
id username email password
1 Timbog Timbog@mail.com vads7y3kkjdfa
2 Marta Marta@mail.com vads7y3kkjdfa

-- default_album
album_id album_name default_user_id
1 Name_Tim 1
3 Katarina 2
-- default_album_img
img_id image_file album_id
3 1320229733.jpg 1
4 3320229733.jpg 3


user_id user_name user_email user_pass user_image
1 Timbog Timbog@mail.com vads7y3kkjdfa 1320229733.jpg
2 Marta Marta@mail.com vads7y3kkjdfa 3320229733.jpg

The approach i used to solve this problem is to first fetch all data by inner join, should i use full outer join and insert the required field to my table, the following query is actual by which i'm trying to make it wor:

INSERT INTO bbpin.users ( user_name, user_pin, user_email, user_password, user_img)
SELECT default_users.username, default_users.bb_pin, default_users.email, default_users.password
FROM bbmpins_pins.default_users
INNER JOIN bbmpins_pins.default_album_images
ON default_album_images.album_id = default_users.id;

i miss the thing how do i compare two table's id in this join maybe? or this query is all wrong by approach?
By two tables which are sepearte in First_DB there could be multiple record how do we trunk them to last entry only ?

Answer Source

It looks like you are attempting to retrieve all rows from the default_users table. And along with each row, also return the corresponding row(s) from default_album table. And along with that, the corresponding row(s) from default_album_img table.

Given the example data, a query using inner joins would return the specified result:

 SELECT u.id             AS user_id
      , u.username       AS user_name
      , u.email          AS user_email 
      , u.password       AS user_pass
      , i.image_file     AS user_image
   FROM default_users u
   JOIN default_album a
     ON a.default_user_id = u.id
   JOIN default_album_img i 
     ON i.album_id = a.album_id

That query will work for the example data.

But, if there is a row in default_user which doesn't have a matching row in default_album, then an inner join won't return that row:

 -- default_users
 id  username    email            password
 3   branstark   bran@winterfell  warg2

Or, if there are two or more rows in default_album that match a given user, then the query will return two copies of the row from default_user...

-- default_album
album_id   album_name  default_user_id
  1        Tim2          1

Without a specification of what is to be returned in those cases, we can't recommend a query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download