Join statements going wrong.

[eluser]Ferdy[/eluser]
Ok, I did some searching, and while I did find some posts with people who needed help with a join, they weren't similar to my problem so here goes.

I'm trying to make a simple news posting system. My posts table has the following fields:

id - this is the id of the post, naturally
postTitle - a field containing the title of the post
postShort - a field containing a short intro for the front page
postFull - a field containing the entire post
postedOn - the date on wich the post was made
postedBy - the id of the user who made the post
postCategory - the id of the posts'category
updatedOn - the date on wich the post was updated
updatedBy - the id of the user who updated the post

Ok, seems pretty simple. Now, here we go.


This piece of code works as intended. It gives me acces to the username and email of the one who made the post. And allows me to show all posts, again, just as intended.

Code:

$this->db->select('posts.*, user.username as poster, user.useremail as posteremail');
$this->db->order_by("posts.id", "desc");
$this->db->join('user as user1', 'user1.id = posts.postedBy');

this piece of code however, does not work as intended:

Code:

$this->db->select('posts.*, user.username as poster, user.useremail as posteremail, user2.username as updater, user2.useremail as updateremail');
$this->db->order_by("posts.id", "desc");
$this->db->join('user', 'user.id = posts.postedBy');
$this->db->join('user as user2', 'user2.id = posts.updatedBy');

It doesn't give me any errors or anything, however, when I use that piece of code, it only shows me posts that have been updated wich, naturally, is not what I want. I want all posts to show, and in a post that has been updated I just want to say something like "this post has been updated by bla bla..."

I tried right, left, outer, inner combinations of all sorts, but nothing is working. So, what is it that I am doing wrong?

To whoever can help me, thanks in advance.

-Ferdy

[eluser]Mirage[/eluser]
At first glance, I'd suggest to alias the first join as well:

Code:

$this->db->select('posts.*, user1.username as poster, user1.useremail as posteremail, user2.username as updater, user2.useremail as updateremail');
$this->db->order_by("posts.id", "desc");
$this->db->join('user as user1', 'user1.id = posts.postedBy');
$this->db->join('user as user2', 'user2.id = posts.updatedBy');

Also I'd suggest you debug $this->db->last_query() to see what query was generated.

Finally - are you sure that the updatedBy field contains a valid id when the posted is new. It'd say it should be the same as the postedBy but if you want to discern the update status as 'never updated' then perhaps make it default to 0 or null and use 'left' on the user2 join.

Do let us know how it goes...

Cheers,
-m

[eluser]Ferdy[/eluser]
Ok, thanks for the suggestions Mirage!
It works now, wich I somehow find kind of weird.

What I did was use you're two suggestions, to alias the first user and make the second one a left join. That did the trick. To be honest, I already tried aliassing both, even in combinations with the different joins. Guess I just used the wrong ones.

Also, the updatedBy and updatedOn already were null by default, much easier to discern if it has been updated that way

[eluser]Mirage[/eluser]
[quote author="Ferdy" date="1221352882"]Also, the updatedBy and updatedOn already were null by default, much easier to discern if it has been updated that way [/quote]

Well that's where your problem was then. If your Foreign Key is null you won't get records from the relation unless you use a left join.

Glad to hear it's working now

-m

[eluser]Ferdy[/eluser]
Ah, that I didn't know. I can make some simple select query's and use where statements, but thats about all of my SQL knowledge

In any case, thanks alot for your help.