I am trying to join 2 tables, retrieve all the data from one table as well as a column from another table on 2 where conditions.
Here are my tables.
Table 1
art_id | artist_id | file | competition_id |
----------------------------------------------------------------
1 | 2 | images/aaa.jpg | 1
2 | 2 | images/bbb.jpg | 1
3 | 5 | images/ccc.jpg | 1
4 | 5 | images/fun.jpg | 1
Table 2
art_id | artist_id | juror_id | score | competiton_id
----------------------------------------------------------------_
1 | 2 | 5 | 10 | 1
As you can see, Table 2 has 1 entry, I need to join table 2 with table 1 to show the score. So my end results should be.
art_id | artist_id | file | competition_id | Score |
-----------------------------------------------------------------------------
1 | 2 | images/aaa.jpg | 1 | 5 |
2 | 2 | images/bbb.jpg | 1 | null |
3 | 5 | images/ccc.jpg | 1 | null |
4 | 5 | images/fun.jpg | 1 | null |
Here is my query, but it just returns the 1 result.
SELECT t1.art_id, t1.artist_id, t1.file, t2.score, t2.juror_id FROM table_one t1 RIGHT JOIN table_two t2 ON t1.art_id = t2.art_id AND t1.artist_id = t2.artist_id WHERE t2.juror_id = 5 AND t1.competition_id = 1