#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    28
    Rep Power
    0

    Question MySQL row compare problem


    Hi Guys,

    I am really struggling with this one. I have two tables as shown below...

    Table 1

    username, user_id, field_id, field_data
    .....a...........1...........1.........46
    .....a...........1...........2.........48
    .....a...........1...........3.........35
    .....b...........2...........1.........46
    .....b...........2...........2.........44
    .....b...........2...........3.........42

    Table 2

    username, user_id, field_id, field_data
    .....z...........200.......151.......46
    .....z...........200.......152.......44
    .....z...........200.......153.......23

    What I am trying to do is compare
    field_id=1 on table 1 to field_id=151 table 2 and if they are the same then 1 and if not then 0
    then compare
    field_id=2 on table 1 to filed_id-152 table 2 and if they are the same then 1 and if not then 0
    and so on.

    The end result I am looking for is a create view that gives me:

    Table 3

    username, user_id, field_id, score
    .....a............1.........1........1
    .....a............1.........2........0
    .....a............1.........3........0
    .....b............2.........1........1
    .....b............2.........2........1
    .....b............2.........3........0

    I cannot seem to figure a way to do this. Can you help me please

    Cheers
    Gogz
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,613
    Rep Power
    1945
    Take a look at this:
    sql Code:
     
    SELECT *
          ,CASE Table1.field_data WHEN Table2.field_data THEN 1 ELSE 0 END
    FROM Table1
    LEFT JOIN Table2 ON Table2.field_id = Table1.field_id+150;


    Notice that I assume field_id is a numeric type (integer). If it is text/varchar, you will have to convert it.

    Comments on this post

    • richpri agrees : Wonderfully elegant!
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    28
    Rep Power
    0

    Thumbs up Huge thanks


    Thanks for the swift reply. I would never have thought of that as a solution. I was thinking it was going to be far more complex. I have run the code and it works perfectly.

    Again thank you very much

    Gogz

IMN logo majestic logo threadwatch logo seochat tools logo