Left Join

with wonky field info

[mooger35]

I'm trying to use a left join on two tables I have but the problem I have is the data I'm joining has a difference in the way it is input.

TABLE: news
news_id ¦ title ¦ content

ex: 1 ¦ This is the title ¦ This is the content etc...

TABLE: ratings
id ¦ total_votes ¦ total_value

ex: News1 ¦ 4 ¦ 17

I'd like to use left join for news.news_id and ratings.id but of course for the news articles I have the added "News" before the id for the ratings table key.

$query = mysql_query("SELECT news.news_id, ratings.total_value, ratings.total_votes, title, content
FROM news LEFT JOIN ratings
ON news.news_id = ratings.id");

How do I prepend 'News' to the join statement?

[LifeinAsia]

It depends on how your DB concatenates strings, but basically something like this should work:
SELECT news.news_id, ratings.total_value, ratings.total_votes, title, content
FROM news LEFT JOIN ratings ON 'News'&news.news_id = ratings.id

An alternate idea- remove the "News" from the other ID. Either in the query or (better) from the entire table.

[mooger35]

Worked great. Thanks!

Edit: spoke too soon. It's now duplicating records with all the differnt ratings.

[mooger35]

Found another way and it seems to work

LEFT JOIN ratings
ON news.news_id = REPLACE(ratings.id,'News','')