Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    28
    Rep Power
    0

    Question Merge user tables query


    One more issue I am trying to fix on my site. I have recently upgraded to a new database and would like to merge all the users and there passwords etc into the new users table. However I cannot find the correct way to do this. I have tried JOINs, UNIONs and even INSERTs with no joy.

    One of the problems is the columns on both tables are not the same. They do have some similarities like id, username etc etc.

    Here is an example of the two tables and a resulting table that I am looking for...

    T1

    id, username, password, reset_count
    10......a.............xyz.............0........
    11......b.............zsd.............1........


    T2

    id, username, password, pone
    1......s.............fgh........33..
    2......d.............ert........45..

    R2

    id, username, password, reset_count, pone
    1.......s..............fgh..........null..........33..
    2.......d..............ert..........null..........45..
    10.....a..............xyz...........0...........null.
    11.....b..............zsd...........1...........null.


    My results so far has duplicated columns which I don't want.

    (note: the old user table has users id of 1-200 while the new one has id numbers of 276 and above so none of these overlap but there is issues with username's etc being the same on both tables).

    Any ideas guys?

    Your help would be appreciated.

    Cheers
    Gogz
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Code:
    CREATE TABLE R2
    AS
    SELECT t1.id
         , t1.username
         , t1.password
         , t1.reset_count
         , t2.pone
      FROM T1 AS t1
    LEFT OUTER
      JOIN T2 AS t2
        ON t2.username = t1.username
    UNION      
    SELECT t2.id
         , t2.username
         , t2.password
         , t1.reset_count
         , t2.pone
      FROM T2 AS t2
    LEFT OUTER
      JOIN T1 AS t1
        ON t1.username = t2.username
    note this will not remove duplicates

    i will tweak it for you if you tell me how to handle same username in both tables with different passwords (which password is kept?) and also which id to use
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    28
    Rep Power
    0

    Superb :)


    I will give the suggest code a try as soon as I can as I am now at work. I should get time soon though. Thanks for the info. Its more complex than I thought it would be lol.

    As for the duplicate data, I don't think it will be an issue as they are my accounts so far. I shall delete any duplicates before I merge the data.

    I shall get back to you in a while on how I get on

    Cheers again
    Gogz
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    if you delete the duplicates before proceeding, then this query is all you will need to create that 3rd table

    it's the classic mysql workaround for a FULL OUTER JOIN
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    28
    Rep Power
    0
    OK i ran the code but I get an error like so...

    #1054 - Unknown column 'T1.id' in 'field list'

    I have double checked and there is definitely a column in both user tables named id so I am unsure as to why it gives me this error. I am running the code without the create part first until I am happy with the outcome but that shouldn't make any difference.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    in order for me to figure out your error, i need to see the results of SHOW CREATE TABLE on both tables
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    28
    Rep Power
    0
    Sorry for my ignorance. How do you mean "result". When I run the query the status bar on MySQL database shows the error I posted. Under that is a copy of the query code and below that is again a copy of the query code.

    It does not show or indicate what line of the code is wrong etc.

    I can post a screenshot I think?

    Cheers
    Gogz
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    run this query --
    Code:
    SHOW CREATE TABLE t1
    repeat for t2
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,400
    Rep Power
    1688
    Why do I think the tables are not actually called T1 or t1?
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    28
    Rep Power
    0
    No you are correct. I changed the names of the tables to make it easier to explain. I thought by T1 you meant table one. I changed the table names in the code you posted to the correct ones. Anyway below is the result of that last code you sent with the correct table one name (sgj_users).


    Table Create Table
    sgj_users CREATE TABLE `sgj_users` (
    `id` int(11) NOT NULL...

    Does this help you?

    Cheers
    Gogz
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by Idunnit1
    Does this help you?
    not if you go "dot dot dot" and remove most of it

    do it for both tables, in full

    then show the actual query you ran

    only then will i be able to make sense of the error message
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    28
    Rep Power
    0
    lol no problem. Here is the query I ran.

    SELECT sgj_users.id
    , t1.username
    , t1.password
    , t1.reset_count
    , t2.pone
    FROM sgj_users AS t1
    LEFT OUTER
    JOIN jos_users AS t2
    ON t2.username = t1.username
    UNION
    SELECT t2.id
    , t2.username
    , t2.password
    , t1.reset_count
    , t2.pone
    FROM jos_users AS t2
    LEFT OUTER
    JOIN sgj_users AS t1
    ON t1.username = t2.username
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    sigh, you neglected to show the tables

    also, there's no way that this query produced the "Unknown column 'T1.id' in 'field list'" error in post #5, because there is no column T1.id in the field list

    however, i did notice that you used sgj_users.id instead of t1.id, which you can't do, since you aliased the table in the FROM clause

    so fix that and try again
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    28
    Rep Power
    0
    oh and I see what you mean with ... but that is what is on the screen. There is no more info there.
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by Idunnit1
    oh and I see what you mean with ... but that is what is on the screen. There is no more info there.
    try a little harder, it's all there
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo