Table joins

Discussion in 'MySQL' started by neilfurry, Oct 27, 2021.

0
  1. #1
    Hi,

    I need some advice please. I need to join two tables together.
    Here is my Table A.
    upload_2021-10-28_14-33-39.png

    I have this Table B
    upload_2021-10-28_14-33-57.png

    and here is the result i expected
    upload_2021-10-28_14-34-19.png

    You will see on the result table i want to maintain the exact number of rows from Table A and if rating is not available for certain person from Table B, it will just be NULL.

    Thank you in advance.
     
    neilfurry, Oct 27, 2021 IP
  2. tech guru

    tech guru Greenhorn

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #2
    SELECT A.eid, A.name, B.rating, B.rateperiod FROM A INNER JOIN B ON A.eid=B.eid
     
    tech guru, Nov 12, 2021 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    28,807
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #3
    that doesn't have a group statement or a way to default rateperiod to 1. This will work better

    select users.eid
   , users.name
   , rating.rating
   , case when rating.rateperiod is null then 1 else rating.rateperiod end as rp
from users left join rating on users.eid = rating.eid
group by users.eid;
    Code (SQL):
    For a working example check out http://sqlfiddle.com/#!9/48f277/9
     
    sarahk, Nov 12, 2021 IP
    Andrii Ozemko likes this.