MySQL how to check if selected column is null before woking with him?

Question

I am working on expanding user profiles for my project, adding more info. I was just adding countries, when I hit a problem. This is my query for selecting all profile data needed:

SELECT c.*, d.username, d.email, e.country_name
    FROM user_profiles c, users d, country e
    WHERE c.user_id = ".$id." AND d.id = ".$id."
    AND e.country_name = (SELECT country_name FROM country WHERE id = c.country_id)

c.* should select all columns from user_profiles.

The $id is an id of user selecting wich profile should be returned. The problem is, keeping registration as simple as possible, I dont have a country selector there. So when this query comes in action, column country_id from user_profiles is empty, that means when I want to return the country_name, the query result is empty, so no profile data is returned.

I have tried to rewrite this with CASE or LEFT JOIN, but I think I am missing something. I want to find if country_id is not null, when it is not, select also country_name with this country_id. Any ideas?

Thank you kindly for responds.

Answer 1

You can use a LEFT JOIN, but it would be so much easier to do that if you started off by using the cleaner and more modern JOIN syntax:

SELECT c.*, d.username, d.email, e.country_name
FROM user_profiles c 
JOIN users d ON d.id = c.id 
JOIN country e ON e.country_id = c.country_id
WHERE c.user_id = 42

Now to solve your problem you can just add LEFT:

LEFT JOIN country e ON e.country_id = c.country_id

Full query:

SELECT c.*, d.username, d.email, e.country_name
FROM user_profiles c 
JOIN users d ON d.id = c.id 
LEFT JOIN country e ON e.country_id = c.country_id
WHERE c.user_id = 42

Related

• Why isn't SQL ANSI-92 standard better adopted over ANSI-89?

Answer 2

Before I even start thinking about your current problem, can I just point out that your current query is a mess. Really bad. It might work, it might even work efficiently - but it's still a mess:

SELECT c.*, d.username, d.email, e.country_name
FROM user_profiles c, users d, country e
WHERE d.id = ".$id." 
AND d.id = c.user_id
AND e.id = c.country_id;

I have tried to rewrite this with CASE or LEFT JOIN

But you're not going to show us your code?

One solution would be to use a sub select against each row in user_profiles/users:

SELECT c.*, d.username, d.email, 
   (SELECT e.country_name
    FROM country e
    WHERE AND e.id = c.country_id LIMIT 0,1) AS country_name
FROM user_profiles c, users d
WHERE d.id = ".$id." 
AND d.id = c.user_id;

Alternatively, use a LEFT JOIN:

SELECT c.*, d.username, d.email, e.country_name
FROM user_profiles c
INNER JOIN users d
   ON d.id = c.user_id
LEFT JOIN country e
   ON e.id = c.country_id
WHERE d.id = ".$id.";