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.

How will this query be rewritten using JOIN?

Discussion in 'MySQL' started by JEET, Sep 15, 2020 at 1:56 AM.

  1. #1
    How will this query be rewritten using JOIN?

    There are 4 tables at least, like these:

    1. PostsTable
    pid, stat, title, etc etc

    2. tagsTable
    id, pid, tag

    3. profilesTable
    profileID, username, etc etc

    4. ratingsTable
    id, pid, profileID, etc etc

    Currently am using:

    select something something
    from postsTable as p, profilesTable as pr, ratingsTable as r, tagsTable as t
    where
    p.stat='y' and
    t.pid = p.pid and
    r.pid = p.pid and
    pr.profileID = r.profileID

    "something something" are columns from profilesTable and from postsTable and from tagsTable.


    I was hoping to do it using join statement, something like this:
    SEMrush
    select something something
    from postsTable as p
    left join tagsTable as t
    on t.pid = p.pid
    left join ratingsTable as r
    on r.pid = p.pid
    ( this is where I am lost )
    No idea how to connect ratingsTable to profilesTable here...
    At this point I have to select username in profilesTable using profileID given in ratingsTable

    I want to avoid creating a new table connecting postsTable to profilesTable for just this purpose...
    profileID is already there in ratingsTable, I am already reading ratingsTable
    So I do not want to create new connecting table for profileID and pid alone and read that also...

    Thanks
     
    Solved! View solution.
    JEET, Sep 15, 2020 at 1:56 AM IP
    Ayoub benali01 likes this.
    SEMrush
  2. #2
    try
    select something something
    from postsTable as p
    left join tagsTable as t on t.pid = p.pid
    left join ratingsTable as r on r.pid = p.pid
    left join profilesTable as pr on pr.profileID = r.profileID
    where p.stat='y'
    Code (markup):
    The joins are all the same, they're just placed differently.
     
    sarahk, Sep 15, 2020 at 3:04 PM IP
  3. JEET

    JEET Notable Member

    Messages:
    3,442
    Likes Received:
    407
    Best Answers:
    16
    Trophy Points:
    235
    #3
    Thanks Sarahk I will try this
     
    JEET, Sep 15, 2020 at 6:30 PM IP