Joining Table Logic

join;select

So i have this four table userId,ApplicationNo,AttachedNo,RequirementNo now the only given data is userId 20170001 how can i join the tables and get all of their data?

Model:

public function get_requirementAttached()
{
$this->db->select('*');
    $this->db->from('user','loanapplication');
    $this->db->where('user.userId', $this->session->userdata('userId'));
    $this->db->join('loanapplication', 'loanapplication.userId = user.userId', 'left');
    $this->db->join('requirementattached', 'requirementattached.ApplicationNo = loanapplication.ApplicationNo', 'left');
    $this->db->join('requirements', 'requirements.RequirementNo = requirementattached.RequirementNo', 'left');
    $query = $this->db->get();       
    return $query->result();

Controller:

$details = $this->foo_require->get_requirementAttached();
$data['ApplicationNo'] = $details[0]->ApplicationNo;
var_dump($data);exit;

Filtered the ApplicationNo here and resulted to null data

SQL:

SELECT * FROM user, application, requirements;
WHERE userId='session(userId)';
FULL JOIN loanapplication ON loanapplication.userId =user.userId ;
FULL JOIN requirementattached ON requirementattached.ApplicationNo =loanapplication.ApplicationNo ;
FULL JOIN requirements ON requirements.RequirementNo =requirementattached.RequirementNo ;

Best Answer

From a SQL perspective, it looks like the query you're trying to create would be:

SELECT *
  FROM user
         LEFT JOIN loanapplication loanapp ON (user.userId = loanapp.userId)
           LEFT JOIN requirementattached reqAtt ON (loanapp.ApplicationNo = reqAtt.ApplicationNo)
             LEFT JOIN requirements req ON (req.RequirementNo = reqAtt.RequirementNo)
 WHERE user.userId = @userid
;

Note: table aliases (loanapp, reqAtt, req) used for readability; the actual query would not have the aliases, and would use the full table names wherever I'm using the aliases.

Also, @userid wouldn't be in the query, that's just acting as a placeholder - you would have the value from $this->session->userdata('userId') here.

The way this query works is it returns all rows (presumably 0 or 1) from user with the specified userId. If there are any rows in loanapplication for this userId, each of those columns is appended to the columns from user. The same process repeats for requirementattached and requirements. Where there is no matching row in one of the LEFT JOINed tables, all columns for that talbe are still present, but their values are all set to NULL

Now - If there's no user record for the session's current user, your result set will have 0 rows in it. If there is a user record, but there are no loanapplication rows with that userId, then loanapplication.ApplicationNo will be NULL. This matches what you've described.

However, you should note that you have at least two columns in your result set named userId. The same hold true for ApplicationNo and RequirementNo. I cannot say for certain whether both of those columns are available through your API, nor which column's value you are accessing.

It's also true that if you had a row for the user, and at least one loanapplication row with that same userId, but there were no requirementattached rows for the ApplicationNo, then loanapplication.ApplicationNo would have a value, but requirementattached.ApplicationNo would be NULL.

As noted in the comments, we can't be entirely certain that the SQL statement being run is the precise one I've shown above, unless we can get your API to show you that statement. We also don't know what your DBMS system is; for some systems at least, there may be a tool that can capture commands sent to the database, and you might be able to find the actual SQL command that way, if absolutely necessary.

If possible, get the actual SQL statement, use a command-line or GUI tool that lets you have an interactive connection with the database, and run the full statement there, so you can see the full results. Then, you'll know for sure whether the results that you're seeing in your application are correct for the current data, or if there's some problem.

A further note: whenever possible, limit the columns you include in the SELECT list to the ones you actually need. It speeds up sending the results from the DB server to your application, and can result in a faster query on the database end. You can also avoid the potential issues of having two columns with the same names in your result set. You'll have to check on your API to determine if you can do this.

Related Question