CODEWITHSUNDEEP
sql
Kenny Scott
Kenny Scott

Reputation: 11

Returning a result when sql query finds no matches

I am pulling a userid from a database named people and have given it a variable of $userid. That same userid is associated with another table named info, sometimes. Sometimes there is a matching userid, sometimes that userid is not associated with the info table. When I do the following query, the function is skipped when the userid in the people table does not have a matching userid in the info table. Is there a way to get a result back when no match is found?

$club_query = $wpdb->get_results( $wpdb->prepare("SELECT clubs FROM info WHERE userid = '$userid' LIMIT 1") );

If the club is in the results, then perform a function.

Any help is appreciated.

Upvotes: 0

Views: 736

Answers (1)

ruakh
ruakh

Reputation: 183201

There are a few dirty hacks I've used for this sort of thing. One is to use MIN(clubs) instead of clubs, since if there are no rows, then MIN(...) returns NULL:

SELECT MIN(clubs) clubs FROM info WHERE userid = ...

Another is to use a RIGHT JOIN (SELECT 1) t:

SELECT info.clubs FROM info RIGHT JOIN (SELECT 1) t ON info.userid = ...

Both approaches work, but have some drawbacks/limitations.

Edited to add: another approach that just occurred to me right now is to use a subquery in the field-list:

SELECT (SELECT clubs FROM info WHERE userid = ...) clubs;

which also converts an empty result-set into NULL. This, too, has some limitations.

Upvotes: 1

Related Questions