I have an issue which I am not sure which approach to take to resolve it. Basically, in a SQL, I have an INNER JOIN clause. I am querying on a specific record, from the main table, combined with an INNER JOIN clause. This means, if the relation does not exist in the join condition then I would end up with zero record instead of one. Usually, if I really want to have one record no matter if the join condition is found or not, I would change INNER JOIN by LEFT JOIN. So, this would allow me to get the record from the main table with the related fields but with null fields on the join condition.
So far so good, but today I have this situation where the INNER JOIN clause is having a WHERE condition. So, my actual SQL is like this:
SELECT Style.Numero,Style.Title_E AS Title,Style.Trans_E,Style.Trans_F,;
Style.DateCrea_E,Style.DateCrea_F,Style.NoMaster,Style.AddName_E,Style.AddName_F,Style.NoStyle,Style.NoStyle2,;
Style.NoMaster2,Style.NoCountry,Style.WikiUrl_E,Style.WikiUrl_F,Style.WikiTitl_E,Style.WikiTitl_F,;
StyleDefinition.Notes_E,StyleDefinition.Notes_F;
FROM Style;_
INNER JOIN StyleDefinition ON Style.Numero=StyleDefinition.NoStyle;
WHERE StyleDefinition.NoStyleC=1 AND Style.Numero=1 ORDER BY Title
In this particular SQL, I cannot just change INNER by LEFT because a WHERE clause is bound to the INNER JOIN clause. How can I modify this SQL to always the record I want from Style.dbf, the one for Style.Numero=1, even if the INNER JOIN is not found?