CODEWITHSUNDEEP
phpmysqlsqlarraysleft-join
Leandro Bardelli
Leandro Bardelli

Reputation: 11578

LEFT JOIN as WHERE IN

I've the following table:

enter image description here

and an array with following codes: A,C,T in php.

What I need is make a mysqli query (no framework) with the following result:

enter image description here

So I can know the values of ids and field and know that there is no value for C, row 2 doesn't care.

Of course, WHERE IN will filter, and something like:

SELECT * FROM test t1 LEFT JOIN test t2 ON t1.idtest = t2.idtest AND t1.code IN ('A','B','C');

will show B but not C.

I look up for other solutions like an USP and passing a varchar(255) as an array, splitting and etc, but I've aprox 1000 codes so there is no way to do that.

Any help or hint involving MySQL or PHP will be preciated, avoiding of course making a loop of 1000 SELECT for each code that is the thing I'm trying to improve. Thanks in advance.

Upvotes: 0

Views: 147

Answers (2)

Don't Panic
Don't Panic

Reputation: 41810

Possible PHP solution, assuming code is unique in your table.

If you use a single SELECT with IN, like this

SELECT * FROM test WHERE code IN ('A','C','T', 'etc.')

You can index by code as you fetch the results

while ($row = $stmt->fetch()) {
    $query_result[$row['code']] = $row;
}

Then iterate the array of codes and fill the result with rows from the query if they exist, or rows with null fields if the query didn't return a row for that code.

foreach ($codes as $code) {
    $result = $query_result[$code] ?? ['idtest' => null, 'field' => null, 'code' => $code];
}

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15941

I'd recommend sticking to just doing the final processing client side. You're probably dumping the results to a local array or html table anyway, just add code to record which choices had matches and add extra entries for the ones that did not. Otherwise, the alternative is something like this....

SELECT t.*
FROM ( SELECT 'A' AS code 
       UNION SELECT 'C' 
       UNION SELECT 'T'
) AS codes
LEFT JOIN test AS t ON codes.code = t.code

...which as you can imagine could get unwieldly with large lists.

Though I am not sure what you were trying to accomplish with the left join to itself.

Upvotes: 1

Related Questions