1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Table joins

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

  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.
    SEMrush
     
    neilfurry, Oct 27, 2021 IP
    SEMrush
  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:
    27,445
    Likes Received:
    4,209
    Best Answers:
    120
    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.