Martin AJ Martin AJ - 2 months ago 6
SQL Question

How can I select the name of author for each post?

I have these two tables:

// posts
+----+---------+------------------------+-----------+------------+
| id | title | content | author_id | visibility |
+----+---------+------------------------+-----------+------------+
| 1 | title1 | content1 | 3 | 1 |
| 2 | title2 | content2 | 2 | 0 |
+----+---------+------------------------+-----------+------------+

// users
+----+---------+----------+
| id | fname | lname |
+----+---------+----------+
| 1 | John | Tersco |
| 2 | Peter | Brlew |
| 3 | Jack | Keveinc |
+----+---------+----------+


I want this is expected output:

+----+---------+------------------------+--------------+
| id | title | content | full_name |
+----+---------+------------------------+--------------+
| 1 | title1 | content1 | Jack Keveinc |
| 2 | title2 | content2 | Unknown |
+----+---------+------------------------+--------------+





Here is my current query:

SELECT p.id, p.title, p.content, CONCAT(u.fname, ' ', u.lname) as full_name
FROM posts p
LEFT JOIN users u
ON p.author_id = u.id AND p.visibility = 1


As you see, all I need is defining
Unknown
when
p.visibility
equals
0
. How can I do that?

Do I need either
CASE
or
COALESCE
or both of them?




EDIT: First of all I have to say Tim Biegeleisen's answer is great and all fine. Just in reality, there is one other column which I need to treat with it exactly like above. But it doesn't have
CONCAT
function. For example:

// users
+----+---------+----------+------------+
| id | fname | lname | reputation |
+----+---------+----------+------------+
| 1 | John | Tersco | 943 |
| 2 | Peter | Brlew | 2300 |
| 3 | Jack | Keveinc | 84 |
+----+---------+----------+------------+


And this is expected result:

+----+---------+------------------------+--------------+------------+
| id | title | content | full_name | reputation |
+----+---------+------------------------+--------------+------------+
| 1 | title1 | content1 | Jack Keveinc | 84 |
| 2 | title2 | content2 | Unknown | N |
+----+---------+------------------------+--------------+------------+


Ok, All I want to know: Still can I use
COALESCE
for
reputation
column? (it doesn't have
CONCAT
)


SELECT COALESECE(reputation, 'N') reputation, ....


Is it fine? Or because it doesn't have
CONCAT
then I have to use
CASE
for it?

Answer

I believe the query below is the best approach, because it will display Unknown for the full_name if either of the following conditions be true:

  • a post has zero visibility (visibility = 0)
  • a post has visibility but cannot be matched to an actual user author


Using the same join condition you originally had, in either of these cases the first and last name of the joined users table would be NULL, and we can display Unknown.

SELECT p.id,
       p.title,
       p.content,
       COALESECE(CONCAT(u.fname, ' ', u.lname), 'Unknown') AS full_name
FROM posts p
LEFT JOIN users u
    ON p.author_id = u.id AND
       p.visibility = 1

Update:

Just using COALESCE on the concatenated first and last name is sufficient, because CONCAT returns NULL if any argument is NULL (q.v. the documentation).

Edit:

If your users table had another column called reputation, then you could also use COALESCE(u.reputation, 'N'). The reason for this is that all columns from the users table will be NULL for a record from posts which does not match.

Comments