I\'m attempting to use JOIN--any type that I can get to work--but receiving somewhat strange results.
I have a table \"article\" (columns \"id\", \"user\", \"title\", and \"body\") and a table \"user\" (columns \"user\" and \"name\").
In those tables, let\'s say, are:
article:
id / user / title / body
1 / joe / monkeys / I like them
2 / kim / crows / they eat monkeys
user:
user / name
joe / Joseph Ape
kim / Kimberly Primate
I would like to have a result that looks like
id / user / title / body / name
1 / joe / monkeys / I like them / Joseph Ape
2 / kim / crows / they eat monkeys / Kimberly Primate
so I use the query
SELECT id, user, title, body, name FROM article LEFT JOIN user ON article.user = user.user
and I get
1 / joe / monkeys / I like them / Joseph Ape
2 / kim / crows / they eat monkeys / NULL
If I use an inner join, I get only the first line.
Oddly enough, if I add another article by user joe, it displays perfectly. For example, if I add values
3 / joe / squirrels / can fly
to the article table, and run the above query, I get
1 / joe / monkeys / I like them / Joseph Ape
2 / kim / crows / they eat monkeys / NULL
3 / joe / squirrels / can fly / Joseph Ape
It seems to me that the join condition article.user = user.user is operating somewhat differently than advertised; instead of setting the columns equal to each other, it appears to take the value of that column in the first row of the user table, and use that as a join condition: i.e., if the user is joe, then display his name; if it is anyone else, do not.
This is as bizarre, I think, as it is frustrating; if anybody has any ideas, I would be profoundly grateful.
cheers
telabrun daggoth