php codeigniter assembling query results

Question

My setup is as follows:

Table 'data': 'title','body',...,'user_id',...  
Table 'users': 'user_id','some_val'...

Basically I want to somehow join the tables 'data' and 'users' on the user_id field so that I can pull 'some_val' from the 'users' table. My problem is that not every user_id in the 'data' table has a corresponding entry in the 'users' table.

With codeigniter/php I ultimately want to assemble a results array of values containing all the 'some_vals' from the 'users' table joining data.user_id = users.user_id. BUT when there exists a user_id in the data table but NOT in the users table, I want to insert some default value into my results array (and hopefully this array can be in the same order as the user_id's in the data table).

Hopefully this wasn't too unclear. Any ideas?

Answer 1

What you'll want to do is what's called a left join. Essentially, this takes all of the rows of data and matches up the users table. Except, if data doesn't have a matching user_id, it just loads null for those columns. In order to deal with those nulls, you can use the coalesce function, which replaces a null with some value (could be 1234 if it's numeric, but I just chose 'DefaultVal' for demo purposes).

Anyway, it all comes together like so:

select
    coalesce(u.some_val, 'DefaultVal') as some_val
from
    data d
    left join users u on
        d.user_id = u.user_id