MySQL gets data from multiple tables

Question

I have a eg_design table with the following columns:

and eg_domains tables, which contain the following columns:

and eg_fonts tables, which contain the following columns:

Now, based on $domain_id I want to get all the data from the eg_design table, some data from the eg_domains table and eg_fonts font_name column value in table but in eg_design table I have on design_font and domain_font columns exist 2 font IDs.

So the query below should give me 2 font_name from the eg_fonts table, like in the eg_design table I have 2 present Font ID.

$get_domain = mysqli_query( $mysqli, "SELECT edg.*, ed.domain_name, egf.* 
  FROM eg_design AS edg 
    LEFT JOIN eg_domains AS ed ON edg.domain_id = ed.domain_id 
    LEFT JOIN eg_fonts AS egf ON egf.font_id = edg.design_font AND egf.font_id = edg.domain_font 
  WHERE edg.domain_id = '$domain_id' ");

But I think for this line

egf.font_id = edg.design_font AND egf.font_id = edg.domain_font

The above query does not work.

I mean I can get all the data but I can't get the font_name column value. It only returns the design_font column value, not the domain_font column value.

Will you help me with this problem?

This is the fiddle: https://www.db-fiddle.com/f/mNscdKDNohpT3xidp3C9Mw/0

Answer 1

View updated answers

https://www.db-fiddle.com/f/mNscdKDNohpT3xidp3C9Mw/5

SELECT edg.*, ed.domain_name, egf.font_name
FROM eg_design edg 
LEFT JOIN eg_domains ed ON edg.domain_id = ed.domain_id 
LEFT JOIN eg_fonts egf ON egf.font_title = edg.design_font 
WHERE edg.domain_id = 1

Answer 2

Basically, you just left join the same table eg_fonts twice.

See dbfiddle. You can try the following query:

$get_domain = mysqli_query( $mysqli, "SELECT edg.*, ed.domain_name, egf.font_name AS design_font_name , egf2.font_name AS domain_font_name
  FROM eg_design AS edg 
    LEFT JOIN eg_domains AS ed ON edg.domain_id = ed.domain_id 
    LEFT JOIN eg_fonts AS egf ON egf.font_id = edg.design_font 
    LEFT JOIN eg_fonts AS egf2 ON egf2.font_id = edg.domain_font 
  WHERE edg.domain_id = '$domain_id' ");

Change this:

LEFT JOIN eg_fonts AS egf ON egf.font_id = edg.design_font AND egf.font_id = edg.domain_font

Regarding:

LEFT JOIN eg_fonts AS egf ON egf.font_id = edg.design_font 
LEFT JOIN eg_fonts AS egf2 ON egf2.font_id = edg.domain_font