left join

prepend data inside join?

[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?

[andrewsmd]

The best way I see to to this is with another intersect table. Since the IDs are different types, I don't know of anyway to do this otherwise. Create and intersect table that is something like news_ratings and have your columns
news_ratings_id ¦ news_id ¦ ratings_id
where news_id and ratings_id are foreign keys to the primary key in the ratings and news tables.
Then you can select news.news_id, ratings.total_value, ratings.total_votes, title, content from news, ratings, news_ratings where news_ratings.news_id = news.news_id AND news_ratings.ratings_id = ratings.id

[mooger35]

Found another way and it seems to work

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

< Parallel discussion here:
[webmasterworld.com...] >

[edited by: tedster at 7:46 pm (utc) on Jan. 6, 2009]