Help With Join

[doublet216]

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

 

 

[requinix]

AND t1.artist_id = t2.artist_id

You told it to match on the artist_id too. There's only one row in table 2 and it has artist_id=2 so all you'll get is results from table 1 with artist_id=2 too.

[doublet216]

Don't think that fixes it,  If I just do

 

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
WHERE t2.juror_id = 5 AND t1.competition_id = 1
 

I still just get the 1 result, I need to get

 

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    |

[Jessica]

Change your RIGHT to LEFT.

[doublet216]

Change your RIGHT to LEFT.

I tried that, no luck.  

[Jessica]

If you run that query in mysql (not PHP) with a left join you WILL get all the rows.

[doublet216]

If you run that query in mysql (not PHP) with a left join you WILL get all the rows.

 

I did run it in mysql, all I get is 1 row

 

 

art_idd

1

artist_id

2

file

images/aaa.jpg

score

5

juror_id

5

[Jessica]

Your original post had the score as 10.

 

Post the actual query, and the actual tables?

[doublet216]

Original Query

 

SELECT t1.art_id, t1.artist_id, t1.file, t2.score, t2.juror_id
FROM competition_artist_artwork t1
LEFT JOIN  competition_artwork_review t2 ON t1.art_id = t2.art_id
WHERE t2.juror_id = 5 AND t1.competition_id = 1

 

Competition Arist Artwork

 

art_id

artist_id

file

competition_id

 

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

 

 

 

Competition Artwork Review

 

art_id

artist_id

juror_id

score

competition_id

 

Table 2

 

art_id | artist_id |   juror_id  |  score  |  competiton_id

-----------------------------------------------------------------

  1      |       2     |        5      |       5   |        1

 

The oringal query should result in

 

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    |

Edited March 21, 2013 by doublet216

[requinix]

Now you're joining on the art_id. Isn't that the primary key?

[Psycho]

SELECT t1.art_id, t1.artist_id, t1.file, t1.competition_id, t2.score
FROM table1 AS t1
LEFT JOIN table2 AS t2
  ON t1.art_id    = t2.art_id
 AND t1.artist_id = t2.artist_id

 

You want ALL the records from table 1, so you should not use a WHERE clause to filter out any records.

 

WHERE t2.juror_id = 5 AND t1.competition_id = 1

 

However, if you want to restrict which records from table 2 are joined on table 1 on those fields you can do that in the JOIN condition. Since some records from t1 do not have corresponding records from t2, the first condition in the where clause will exclude all of those records.

SELECT t1.art_id, t1.artist_id, t1.file, t1.competition_id, t2.score
FROM table1 AS t1
LEFT JOIN table2 AS t2
  ON t1.art_id    = t2.art_id
 AND t1.artist_id = t2.artist_id
 AND t2.juror_id = 5
 AND t1.competition_id = 1

Edited March 21, 2013 by Psycho

[Barand]

It's only the t2 condition that needs to be in the JOIN clause. Conditions on t1 can stay in the WHERE

 

SELECT t1.art_id, t1.artist_id, t1.file, t1.competition_id, t2.Score
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.art_id = t2.art_id
    AND t1.artist_id = t2.artist_id
    AND t2.juror_id = 5
WHERE t1.competition_id = 1

[doublet216]

 

It's only the t2 condition that needs to be in the JOIN clause. Conditions on t1 can stay in the WHERE

 

SELECT t1.art_id, t1.artist_id, t1.file, t1.competition_id, t2.Score
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.art_id = t2.art_id
    AND t1.artist_id = t2.artist_id
    AND t2.juror_id = 5
WHERE t1.competition_id = 1

 

Thanks!   It's been sometime since I have done actual queries.