Generate SQL and joins

[aTekTipsUser]

I have been trying to determine the best way to generate select statements and insert the correct joins based on the tables selected. I have all the joins, but determining which joins to add to the statement has become very lengthy. Has anyone come up with a easy way to add joins to the sql statement based on the tables selected?

 

[techsmith]

You would need to know the exact question being asked in order to determine the join type. You could join tableA to tableB on a related attribute using an inner, left outer, right outer or cross join. All 4 would yield different results (depending on the data) each appropriate to a different question.

You need to retrieve the question in a way that can be translated to SQL. If a user asked a question in plain English you might be able to code some sort of translation tool. A query building GUI is essentially another way to retrieve the question and translate it to SQL.

A simpler approach would be to use pre-defined views and add build the SELECT, WHERE, GROUP By, ORDER BY etc clauses based on information from the user.