Bizarre JOIN behavior

[telabrun]

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

[pallevillesen]

It means that in one table is says

 

\'Kim\' another place it says \'Kim \' or something similar... (note the space character in the last instance)...

 

You should simply use an integer for users (i.e. an USERID instead of the name) since integer values are better for joining... The code is correct, and it does what it should....

 

But for some reason, the article.user <> user.user when it comes to \"kim\".... How is the article.user value generated ? Is it selected from the user table and then directly copied and INSERTed into the article table ?

 

So... add an auto_increment field to the users table and then use that id value in your articles table instead of the user column.... You may still check for unique usernames etc. when creating new users...

 

P.

[telabrun]

The article.user value is, essentially an \"author\" field, and it is generated immediately when a new row is added to the article table. This is ensured by a login.

 

Perhaps a clearer statement of my question is:

 

What query can I use in order to obtain a result that returns the names of all the users, not just the first one, without adding any other columns to either table?

 

Quite simply, I would like to obtain this result:

 

id / user / title / body / name

 

1 / joe / monkeys / I like them / Joseph Ape

2 / kim / crows / they eat monkeys / Kimberly Primate

3 / joe / squirrels / can fly / Joseph Ape

 

The only difference here is that Kimberly Primate\'s name appears (i.e., is JOINed); with all of the queries I\'ve tried so far, that cell is NULL.

 

It seems to me that a JOIN should exist to perform this, without too much difficulty. The mySQL manual suggests as much, but the subtleties of the JOIN syntax, apparently, elude me.

 

Thanks again.

 

telabrun daggoth

[pallevillesen]

It is clear what you want, and the code is correct... You COULD try and specify table also:

 

SELECT a.id, a.user, a.title, a.body, u.name FROM article a LEFT JOIN user u ON article.user = user.user;

 

... since you have two coloumns called user... But basically you\'re selecting ALL rows from article, then JOINing rows from user to this table, where the coloumn \"user\" is identical, if NO row exists in the user table with the current article.user value, NULL is returned....

 

P.

[telabrun]

Okay, so I\'ve been playing with this a little bit, and now I\'m looking at something of a simpler query.

 

It no longer involves JOIN; I believe I\'ve tracked the problem to something in the manner in which PHP interacts with SELECT in a FOR loop.

 

I understand that it\'s probably bad style to perform the JOIN-type operation in two steps, and I know for certain that it\'s slower than a JOIN operation. However, I\'m trying to get this to work in any way that I can.

 

for($k = 1; $k <= $nrows; $k++) {



$query = "select id, time, title, user from article where id=\'$k\'";



$return = mysql_query($query);

$row = mysql_fetch_array($return);

extract($row);



$query = "select fname, lname from user where user=\'$user\'";

echo $query;



echo "<p></p>";

}

 

produces:

 

select fname, lname from user where user=\'joe\'

 

select fname, lname from user where user=\'kim

 

select fname, lname from user where user=\'dale

 

select fname, lname from user where user=\'joe\'

 

Notice that the second single quote is missing for all user except joe (who happens to be the user that occupies the first row).

 

What gives?

 

Thanks again.

 

telabrun daggoth

[pallevillesen]

Instead of echo $query, try and say this:

 

echo \"X\".$user.\"X<P>\";

 

Check carefully if your db contains what it is supposed to.

 

And... this is NOT a good way to do mysql lookups... it way too slow... You should get your joins to work properly instead, and the original code is good... I don\'t have your full original code and unfortunately no time...

 

I\'m back friday, so if you\'re still stuck then post your full code and a \"describe article\", \"describe user\" (sql statements, so we may copy your db schema) then I\'ll have a look. But I\'m pretty sure this is a data problem, not an sql problem (if you understand what I mean).

 

I hope this helps,

P.