How to join four tables with MySQL

Question

I have omc_projects, omc_logs,omc_specs and omc_files.

All of omc_logs,omc_specs and omc_files table has a field called project_id.

CREATE TABLE IF NOT EXISTS `omc_projects` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY (`id`)
) ... ;

CREATE TABLE IF NOT EXISTS `omc_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
 ...
  PRIMARY KEY (`id`)
) ... ;


CREATE TABLE IF NOT EXISTS `omc_logs` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `project_id` int(10) NOT NULL,
  ...
  PRIMARY KEY (`id`)
) ... ;


CREATE TABLE IF NOT EXISTS `omc_specs` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `project_id` int(10) NOT NULL,
  ...
  ...
  PRIMARY KEY (`id`)
) ... ;

Now I am making a delete function in a model to check if there are any files, specs or logs like this.

function checkproject($id){
// if there are any data in one of table, returns TRUE 

$query = 'SELECT omc_projects.id, omc_specs.id, omc_logs.id, omc_files.id
FROM omc_projects
JOIN omc_specs
         ON omc_specs.project_id = omc_projects.id
JOIN omc_logs
        ON omc_logs.project_id = omc_projects.id
JOIN omc_files
        ON omc_files.project_id = omc_projects.id
WHERE omc_projects.id = $id'

        $query = $this->db->get();
        if ($query->num_rows() > 0){              
                return TRUE;
        }else{
            return FALSE;
        }
        $query->free_result();

     }

If there are even one data in any table, I want to return TRUE, and if there are none returns FALSE.

However this works if there are data in all tables. If there is one in specs or any other tables, it does not return TRUE.

How should I join these tables? LEFT or INNER ??

Thanks in advance.

Answer 1

You should use outer join.