How to get INNER JOIN result from two tables

Question

I have the three MySql tables :

1. table_fonts

   • font_id (int)
   • font_name (varchar)

2. table_charsets

   • charset_id (int)
   • charset_name (varchar) (examples: 1=latin, 2=greek, and so on)

3. table_charsets_by_font

   • font_id (int)
   • charset_id (int)

I am having trouble into creating my INNER JOIN query to obtain a list with all charsets (table_charsets.charset_name) listed in columns, with a value of 1 if existing, or a value of 0 if not existing — aka a TINYINT —, like this :

font_id | font_name | charset_id1 | charset_id1_existing | charset_id2 | charset_id2_existing ... and so on

For example :

1 | Arial           | 1 | 1 | 2 | 1 ... and so on
2 | Arial Black     | 1 | 1 | 2 | 1 ... and so on
3 | Times New Roman | 1 | 1 | 2 | 0 ... and so on

So far, all my attempts have failed. Could someone point me to the right direction ? Thank you :)

Answer 1

Please try:

SELECT tf.font_id, tf.font_name, tcf.font_id, tc.charset_name, tcf.font_id, tcf.charset_id  
FROM table_fonts tf 
INNER JOIN table_charsets_by_font tcf ON tcf.font_id=tf.font_id
INNER JOIN table_charsets tc ON tc.charset_id=tcf.charset_id
WHERE tc.charset_name='latin';