Select from Multiple table, but ignore if not found

Question

Using this query I'm selecting rows from multiple tables. Unfortunately, if a row does not exist in one table, then rows in all tables won't return. It's because I'm using AND operator.

So, I want this query to be modified, where will ignore a table if value is not found, but return rest of the tables where the value is found.

Here's the MySQL query:

   foreach ($courseArr as $term) {
$term = trim($term);
if (!empty($term)) {

        $courseSectionSql[] = "courseDataApp.course = '$term' AND courseDataApp.section = '$sectionArr[$i]'";

    $i++;

}

}

$data = $db->rawQuery("SELECT courseDataApp.*, facultyDatabase.*, books.* 
                        FROM courseDataApp
                        INNER JOIN facultyDatabase ON facultyDatabase.initial = courseDataApp.faculty
                        INNER JOIN books           ON books.course = courseDataApp.course  WHERE ".implode(' OR ', $courseSectionSql));

Here's what is returns:

 {
"id":11,
// courseDataApp values 
"faculty":"AKH1", 
"course":"CSE241",
"section":"7",
.
.
.
.
.
.
.

// facultyDatabasevalues     
"initial":"AKH1",    
"name":"Ms. Kamal Habi",
"dept":"ECE",
.
.
.
.
.
.




// books values

"books": "Digital Logic Design"
},

So the problem is, when a value from facultyDatabase or books tables not found, rest of the data won't return. I just want it to ignore that, show what's found. Like Union.

Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Nov 27 '18 at 16:42

score 1 · Accepted Answer · answered Nov 27 '18 at 16:53

1

As some of the comments point out you are using outdated syntax that INNER JOINs the tables which leads to the result you get. You need to LEFT JOIN the tables. Hence you will have a result even though there are no entries in the LEFT JOINed tables. Something like this should work

SELECT courseDataApp.*, facultyDatabase.*, books.*
FROM courseDataApp
LEFT JOIN facultyDatabase ON facultyDatabase.initial = courseDataApp.faculty
LEFT JOIN books           ON books.course = courseDataApp.course
    WHERE courseDataApp.course = '$term' 
      AND courseDataApp.section = '$sectionArr[$i]'

Written like this you would have an equivalent to your query in current syntax that will not return anything if there are no entries in the INNER JOINed tables.

SELECT courseDataApp.*, facultyDatabase.*, books.* 
FROM courseDataApp
INNER JOIN facultyDatabase ON facultyDatabase.initial = courseDataApp.faculty
INNER JOIN books           ON books.course = courseDataApp.course
    WHERE courseDataApp.course = '$term' 
      AND courseDataApp.section = '$sectionArr[$i]'

answered Nov 27 '18 at 16:53

rf1234

1,510
12
13

Hi, thank you for the solution, I updated the question. Please check it. I'm using OR statement for multiple match. – Tahmid Nov 27 '18 at 17:04
Again: This is outdated syntax; you should not use that. – rf1234 Nov 27 '18 at 17:05
Please check now, I updated the syntax with yours. Still returning like before(where row exists in all 3 tables) – Tahmid Nov 27 '18 at 17:08
In my reply I mentioned that using INNER JOIN will produce the result you have right now. And that is what it does! You have just confirmed it! You need to copy the code using LEFT JOIN; then it will work! Please read this to understand the difference between LEFT JOIN and INNER JOIN: https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join – rf1234 Nov 27 '18 at 17:10

Select from Multiple table, but ignore if not found

1 Answers1