How to fetch multiple columns from multiple table and show data on non empty columns and null or zero on empty columns using joins in MYSQL

I would like to know how to fetch multiple columns from multiple table even if one column in one table does not exist and return the columns which ever exist in one or more table used for joins

Ex:

Tables:

user_pr_details:
 [id] [first_name] [last_name]
urname: 
 [id] [email] [username]
profile_pic:
 [id] [file_name ]

SQL:

SELECT a.first_name, a.last_name, b.username, c.file_name 
FROM user_pr_details a,urname b,profile_pic c 
WHERE a.user_id = b.urname_id 
and b.urname_id = c.user_id 
and c.user_id in (7,8,9)

Problem:

for user_id in (7,8) all the columns in all three table exists..
whereas for user_id in (9) [file_name] doesn't exists in profile_pic table
so the query returns two rows for user_id in (7,8)

What I need? If a column doesn’t exist in one table then return the rest of the columns which does have the data and show either null or zero in the place of non-existing col.

Answer

I would like to suggest this query instead.

SELECT
      upd.first_name
    , upd.last_name
    , u.username
    , pp.file_name
FROM urname u
LEFT OUTER JOIN user_pr_details a ON u.urname_id = upd.user_id
LEFT OUTER JOIN profile_pic pp ON u.urname_id = pp.user_id
WHERE u.urname_id  IN (7, 8, 9)

Among the first choices you need to make when creating a SQL query is the “from table”. That is, the first table you will reference. This is an important choice! and ideally this table “will always be available” to the query and the other tables “will relate but not control” the query logic.

This query is about users, so the primary table about users should be “the from table”.

After that we need to “join” the other information, you ARE already doing joins but you do this in an old fashioned manner through the where clause with join conditions like a.user_id = b.urname_id (although this exists in the where clause it is still a join condition). I have used ANSI 92 join syntax instead and one of the benefits of doing this is that I can easily use an “outer” join. These join types allow a record to exist in one table but not exist in the other table. A LEFT OUTER JOIN refers to the “from table” as being on the left so records may exist in table urname without any matching records in either user_pr_details or profile_pic.

I recommend this visual guide to joins if unfamiliar with this syntax, but do note “OUTER” is an optional word, so a LEFT OUTER JOIN is quite commonly just “LEFT JOIN”

Notice how I have also changed the WHERE clause. We want to ensure that the way we filter the information work on the “from table” if possible because that way we allow NULLs from the other tables.

---

small note: I used different table aliases. I prefer these to have a relationship with the table name so ‘u’ for urname; ‘upd’ for user_pr_details; and, ‘pp’ for profile_pic. Personally I think a, b, c are too abstract and if you need to adjust the query structure then they can get out of sequence easily (as they did for me!)