MySQL Retrieving data from two tables using inner join syntax

mysql, sql

Solution

try this:

SELECT  a.Event_ID, 
        a.Competitor_ID,
        a.Place,
        COALESCE(b.money, 0) as `Money`
FROM    entry a left join prize b
            on  (a.event_id = b.event_ID) AND
                (a.place = b.Place)

hope this helps.

EVENT_ID    COMPETITOR_ID   PLACE   MONEY
101           101            1      120
101           102            2       60
101           201            3       30
101           301            4        0   -- << this is what you're looking for
102           201            2        5
103           201            3       40

Problem

My two tables are ``` Entry event_id competitor_id place 101 101 1 101 102 2 101 201 3 101 301 4 102 201 2 103 201 3 ``` second table lists prizes on offer for the events ``` Prize event_id place money 101 1 120 101 2 60 101 3 30 102 1 10 102 2 5 102 3 2 103 1 100 103 2 60 103 3 40 ``` From this I am looking to show all the information from the Entry table alongside the amount of money they won for their respected placing. If they failed to place in the money then a 0 will be displayed. Any help would be appreciated.

Original source