sqlinner-joinheidisql

SQL Query inner join with specific data


SELECT players.`name`, multijobs.jobdata
FROM players
INNER JOIN multijobs
ON players.citizenid = multijobs.citizenid
SELECT * FROM multijobs WHERE jobdata LIKE '%police%';

So table multijobs does not show player names only citizen id's, I just want to list the name of the player which is in the table players next to their jobdata which is in their multijobs table.


Solution

  • You can filter out the results using where clause after the Join

    SELECT players.`name`, multijobs.jobdata
    FROM players
    INNER JOIN multijobs
    ON players.citizenid = multijobs.citizenid
    WHERE multijobs.jobdata LIKE '%police%'
    
    

    or you can perform the filter directly on the Join when dealing with inner joins

    SELECT players.`name`, multijobs.jobdata
    FROM players
    INNER JOIN multijobs
    ON players.citizenid = multijobs.citizenid
    and multijobs.jobdata LIKE '%police%'
    

    Although filtering on Join in this case will return same results but if the join is for e.g left join then this might produce different results depending on what you need.