CODEWITHSUNDEEP
mysqlsql
Martin AJ
Martin AJ

Reputation: 6697

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?

Upvotes: 1

Views: 106

Answers (6)

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

SELECT p.id, p.title, p.content, case p.visibility when 0 then  'Unknown' else CONCAT(u.fname, ' ', u.lname) end as full_name
FROM posts p
LEFT JOIN users u
ON p.author_id = u.id

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521513

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.

Upvotes: 3

K.K
K.K

Reputation: 366

You have to use Case while selecting the columns,

SELECT p.id, p.title, p.content,case when p.visibility=0 then 'Unknow' 
else CONCAT(u.fname, ' ', u.lanme) end as full_name
FROM Posts p
JOIN users u
ON p.author_id = u.id

Upvotes: 1

mm759
mm759

Reputation: 1424

You can replace CONCAT(u.fname, ' ', u.lname) by CASE WHEN u.id IS NULL THEN 'unknown' ELSE CONCAT(u.fname, ' ', u.lname) END.

Upvotes: 1

Mincong Huang
Mincong Huang

Reputation: 5552

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

Upvotes: 1

Jens
Jens

Reputation: 69450

Case do the Job:

SELECT p.id, p.title, p.content, case p.visibility when 0 then  'unknown' else CONCAT(u.fname, ' ', u.lname) end as full_name
FROM posts p
LEFT JOIN users u
ON p.author_id = u.id

Upvotes: 1

Related Questions