CODEWITHSUNDEEP
mysqlsql
pipechaves
pipechaves

Reputation: 73

How to show results from one table joined to a second table, even if the second table doesn't have any matching records?

I have 2 tables.

Table 1

Table1

Table 2 has comments for the table 1, where the FK is the ID_tb1:

Table2

I'm trying to get a query in order to get all the comments to show as 1 table. If there is no comment in table 2 then there should be no comment but the data from table 1 should still be shown.

Here is an example of how the result should be

Table result

Upvotes: 1

Views: 49

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28874

  • Do Left join using Table 1 as starting table, so that all the records from Table 1 come.
  • Using Concat function, you can concatenate comment and datetime in a string.
  • Using Group_concat aggregation function, you can concat all the rows by a separator.

Try the following:

Select t1.id, t1.letter
       Group_concat(concat(t2.datetime, ' ', t2.comments)) as Comment 
From table1 AS t1 
LEFT JOIN table2 AS t2 ON t2.id = t1.id 
GROUP BY t1.id, t1.letter

Upvotes: 1

Related Questions