MySQL JOIN a table or another

Question

Context:
The database is about phones. I have a search engine in a TABLE sav (records of "after sales service") which has a field phone containing the IMEI (its unique id) of the phone.
In some case, the phone is going back in our stock (deleted from the TABLE sales and inserted into the TABLE stock) and an other phone is returned to the customer.

Issue:
In the search engine of the "after sales service", I use to LEFT JOIN sales, but in case a phone has gone back in stock, it won't be found by its IMEI.

To sum:

I need to join a table if a record is found, else, join an other table.

My actual query:
I shortened it ([...]) to make it readable:

SELECT sav.id, sav.phone, [...] FROM sav 
LEFT JOIN sales ON (sav.phone = sales.imei)
LEFT JOIN stock_model ON (sales.model = stock_model.id) [...]
WHERE (sales.imei LIKE :search_1 OR [...])
OR (sales.imei LIKE :search_2 OR [...])
OR [...]

I need to change the 2nd line but I don't understand how. The questions I read before seem to answer specific cases.

EDIT: table structure & dummy data
I won't post irrelevant fields (too many)
TABLE sav

| id | phone     | issue   | replacement_imei | ...
----------------------------------------------------
| 35 | 123456789 | battery | NULL             | ...     <-- phone n°1
| 54 | 987654321 | screen  | 147258369        | ...     <-- phone n°2 replaced by phone n°3

TABLE stock

| id  | imei      | model | color | memory | ...
-------------------------------------------------
| 68  | 369258147 | 2     | 4     | 1      | ...
| 92  | 987654321 | 5     | 8     | 3      | ...     <-- phone n°2 replaced by phone n°3

TABLE sales

| id  | imei      | model | color | memory | ...
-------------------------------------------------
| 68  | 123456789 | 2     | 4     | 1      | ...     <-- phone n°1
| 103 | 147258369 | 5     | 8     | 3      | ...     <-- phone n°3 replaced phone n°2

EDIT: complete search engine [PHP]:
In case the whole function helps:

$keywords = explode(" ", $keywords);
$query = "SELECT sav.id, sav.phone, sav.date, sav.nom, sav.issue, sav.replacement_imei, sav.commentaire, sav.attente, sav.pieces, sav.frais, sav.date_envoi, sav.tracking, sav.termine FROM sav LEFT JOIN sales ON (sav.phone = sales.imei) LEFT JOIN stock_memory ON (sales.memory = stock_memory.id) LEFT JOIN stock_color ON (sales.color = stock_color.id) LEFT JOIN stock_model ON (sales.model = stock_model.id) LEFT JOIN stock_grade ON (sales.grade = stock_grade.id) WHERE ";

// Makes a long query returning each row where at least 1 keyword matches 1 column
$query_array = array();
    for ($i = 0; $i < count($keywords); $i += 1) {
        $query .= " ( sales.imei LIKE :search_" . $i;
        $query .= " OR ( sales.snowden = 1 AND 'snowden'= :search_" . $i . " )";
        $query .= " OR stock_model.name LIKE :search_" . $i;
        $query .= " OR stock_color.name LIKE :search_" . $i;
        $query .= " OR stock_memory.amount LIKE :search_" . $i;
        $query .= " OR stock_grade.name LIKE :search_" . $i;
        $query .= " OR sav.id LIKE :search_" . $i;
        $query .= " OR DATE_FORMAT(sav.date, '%d/%m/%Y') LIKE :search_" . $i;
        $query .= " OR sav.nom LIKE :search_" . $i;
        $query .= " OR sav.issue LIKE :search_" . $i;
        $query .= " OR sav.replacement_imei LIKE :search_" . $i;
        $query .= " OR sav.commentaire LIKE :search_" . $i;
        $query .= " OR sav.attente LIKE :search_" . $i;
        $query .= " OR sav.pieces LIKE :search_" . $i;
        $query .= " OR sav.frais LIKE :search_" . $i;
        $query .= " OR DATE_FORMAT(sav.date_envoi, '%d/%m/%Y') LIKE :search_" . $i;
        $query .= " OR sav.tracking LIKE :search_" . $i;
        $query .= " OR ( sav.termine = 1 AND 'termine'= :search_" . $i . " )";
        // $query .= "LIKE :search_" . $i;
        if ($i != (count($keywords) - 1)) {
            $query .= " ) AND ";
        } else {
            $query .= " ) ";
        }
        if (strtolower($keywords[$i]) == 'snowden' || strtolower($keywords[$i]) == 'termine') {
            $query_array['search_' . $i] = $keywords[$i];
        } else {
            $query_array['search_' . $i] = "%" . $keywords[$i] . "%";
        }
    }
    $query .= "ORDER BY sav.id DESC LIMIT $offset, $limit";
    $results = $this->bdd->prepare($query);
    $results->execute($query_array);
    return $results;

Please share the table structures and some dummy data reflecting the real data post these as ascii data tables not images., Also post the expected outout as a ascii data table. — Raymond Nijland, Jan 25 '18 at 11:24
Hi @RaymondNijland, I edited my post with the relevant fields. You can see `sav.phone` matches with `stock.imei`/`sales.imei` — AymDev, Jan 25 '18 at 11:35

Başar Kaya · Answer 1 · 2018-01-25T15:39:06.240

0

First I get all data with full outer join, then Sales.Model => Select all ID from Stock_Model.

SELECT sav.id, sav.phone, [...] FROM sav 
FULL OUTER JOIN sales ON (sav.phone = sales.imei)
WHERE sav.phone = sales.imei or sales.model in (select Id from stock_model)

edited Jan 25 '18 at 15:39

answered Jan 25 '18 at 11:25

Başar Kaya

354
6
13

Hello ! You can't get the `stock_model.id` if you haven't the phone. There is no `sav.model` field – AymDev Jan 25 '18 at 11:39
@AymDev I edited my answer can you check this one. – Başar Kaya Jan 25 '18 at 15:42
The issue I struggle with isn't about the `stock_model` table. I want to join `sav.phone` on `sales.imei` OR `stock.imei`. The `WHERE` clause has to be as I placed it, there's only `JOIN`s before it. After it are the multiple conditions of the search engine. – AymDev Jan 25 '18 at 16:08
I don't mind where clause because you can add any expression after. I want to help you for joins. – Başar Kaya Jan 25 '18 at 16:56

MySQL JOIN a table or another

1 Answers1