MySQL get data from the multiple table-CodePudding

I have an eg_design table which has the below columns:

and eg_domains table which has the below columns:

and eg_fonts table which has the below columns:

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

So,below query should give me 2 font_name from the eg_fonts table as on the eg_design table I have 2 font IDs exist.

$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 is not working.

I mean I can get all data but could not get the font_name column value. It's return only design_font column value not the domain_font column value.

Could you help me to fix it?

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

CodePudding user response：

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

See dbfiddle . You can try the query below:

$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

To this:

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

CodePudding user response：

See updated answer

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