I’ve created few views that are linked and among all the views, there’s specific column that I can use for joining. The views will be generated dynamically with C# and for that, I’ve created some sample types according to database table column:

public class CustomDataType
{
   public string ValType { get; set; }
   public string From { get; set; }
}

public class CustomDataType2
{
    public string VALTypeForeignKey { get; set; }
    public string Join { get; set; }
    public string Where { get; set; }
} 

The data retrieved looks as follows:

 public static List < CustomDataType > GetJoinData() {
   List < CustomDataType > aLst = new List < CustomDataType > () {
     new CustomDataType {
       ValType = "VAL1", From = "VW_1 m "
     },
     new CustomDataType {
       ValType = "VAL2", From = "VW_2 q "
     },
     new CustomDataType {
       ValType = "VAL3", From = "VW_3 r "
     }
   };

   return aLst;
 }

 /*VW_1 m
  *VW_2 q
  *VW_3 r 
  *VW_4 s 
  *VW_5 u 
  *VW_6 v*/

 public static List < CustomDataType2 > GetFinalJoinData() {
   List < CustomDataType2 > aLst = new List < CustomDataType2 > () {
     new CustomDataType2 {
       VALTypeForeignKey = "VAL1", Join = "m.col4 = q.col4", Where = ""
     },
     new CustomDataType2 {
       VALTypeForeignKey = "VAL1", Join = "m.col4 = r.col4", Where = ""
     },

     new CustomDataType2 {
       VALTypeForeignKey = "VAL2", Join = "q.col4 = m.col4", Where = ""
     },
     new CustomDataType2 {
       VALTypeForeignKey = "VAL2", Join = "q.col4 = r.col4", Where = ""
     },

     new CustomDataType2 {
       VALTypeForeignKey = "VAL3", Join = "r.col4 = m.col4", Where = ""
     },
     new CustomDataType2 {
       VALTypeForeignKey = "VAL3", Join = "r.col4 = q.col4", Where = ""
     }
   };

   return aLst;
 } 

As it’s a console application, in the main method I did the following (Though it’s pretty messy, will be trying to improve the code snippet):

string sqlSelect = "VAL1_COL2_VAL2_COL4";

List < string > dimData = new List < string > ();
string[] splitString = sqlSelect.Split('_');

foreach(var item in splitString) {
  dimData.Add(item);
}

dimData.RemoveAll(x => x.Contains("COL"));

List < string > from = new List < string > ();
List < string > Join = new List < string > ();

foreach(var item3 in dimData) {
  foreach(var item in GetJoinData().Where(c => c.DimType == item3)) {
    from.Add(item.From);

    foreach(var item2 in GetFinalJoinData().Where(c => c.DIMTypeForeignKey == item.DimType)) {
      Join.Add(item2.Join);
    }
  }
}

string str = " From " + string.Join(",", from) + "where " + string.Join(" = ", Join.Distinct()) + " AND ";

Console.WriteLine(str);

The output is as follows when I run the above code:

From VW_1 m ,VW_2 q where m.col4 = q.col4 = m.col4 = r.col4 = q.col4 = m.col4 = q.col4 = r.col4 AND

Expected Output: For VAL1 and VAL2

 From VW_1 m ,VW_2 q where m.col4 = q.col4 = and q.col4 = m.col4

For VAL1, VAL2 and VAL3

 From VW_1 m ,VW_2 q, VW_3 r where m.col4 = q.col4 = and q.col4 = m.col4 and r.col4 = m.col4 and r.col4 = q.col4 (I can have one of them either r.col4 = m.col4 or r.col4 = q.col4)

As you can see, in the query that my code generated, the alias with r is also there but it should retrieve data with the alias m and q as those are referred to VAL1 and VAL2, are filtered from the string sqlSelect itself. I am not sure how I can exclude that and make that a specific query with proper placing of and as well as where clause?