How to correctly add the rows from a table that were linked by an id to another table on MySQL?-CodePudding

Note: The following information was created artificially just for educational purposes.

Suppose that the following query:

SELECT author, is_public, content FROM dapp.messages WHERE is_public = '0'

gets the following table:

| author| is_public      | content            |
-----------------------------------------------
|  3240 |0               |Hello, I'm Bertha   |
|  4039 |0               |Hello, I'm Kristina |
|  4810 |0               |Hello, I'm April    |

Now, in this case, the numbers in the column author are linked to a row in another table called credentials , which means that:

3240 is linked to this row in credentials table:

|   id  | first_name   | member_type   |
----------------------------------------
|  3240 |Bertha        | regular       |

4039 is linked to this row in credentials table:

|   id  | first_name   | member_type   |
----------------------------------------
|  4039 |Kristina      | regular       |

4810 is linked to this row in credentials table:

|   id  | first_name   | member_type   |
----------------------------------------
|  4039 |April         | regular       |

So, I would like to know a correct way of adding the first name column from credentials table to the table obtained by the first query in this post, ending up with an output like this:

| author| is_public      | content            | first_name   |
--------------------------------------------------------------
|  3240 |0               |Hello, I'm Bertha   |Bertha        |
|  4039 |0               |Hello, I'm Kristina |Kristina      |
|  4810 |0               |Hello, I'm April    |April         |

As a newbie, I thought that by trying the following query, I would get the desired output from above:

SELECT t1.*, t2.*
FROM ( SELECT author, is_public, content  FROM dapp.messages WHERE is_public = '0')
t1 CROSS JOIN ( SELECT first_name FROM dapp.credentials WHERE id IN (SELECT author FROM dapp.messages)) t2

But I ended up creating a monstruosity that threw this output:

| author| is_public      | content            | first_name   |
--------------------------------------------------------------
|  3240 |0               |Hello, I'm Bertha   |Bertha        |
|  3240 |0               |Hello, I'm Bertha   |Kristina      |
|  3240 |0               |Hello, I'm Bertha   |April         |
|  4039 |0               |Hello, I'm Kristina |Bertha        |
|  4039 |0               |Hello, I'm Kristina |Kristina      |
|  4039 |0               |Hello, I'm Kristina |April         |
|  4810 |0               |Hello, I'm April    |Bertha        |
|  4810 |0               |Hello, I'm April    |Kristina      |
|  4810 |0               |Hello, I'm April    |April         |

Which I didn't get at all, so I'm stuck at here.

CodePudding user response：

I'm not sure why you think you need a cross join here, it appears you need a simple inner join:

select m.author, m.is_public, m.content, c.first_name
from dapp.messages m 
join dapp.credentials c on c.id = m.author
where m.is_public = '0';

As you want a single column a correlated subquery is also a viable option:

  select m.author, m.is_public, m.content, 
    (select first_name from dapp.credentials c where c.id = m.author) as first_name
  from dapp.messages m 
  where m.is_public = '0';