Does anyone have an example of a User Interface for creating a SQL Where clause?

Question

I'm having trouble trying to map nested conditions onto an intuitive interface.

eg. How would you represent ((Condition1 AND Condition2) OR (Condition1 AND Condition5)) AND Condition4

Answer 1

Here's a screenshot of prototype I did for a linux app a few years ago. You could click on the +/- icons to add rows to a group and click on the "add new..." and "remove last..." buttons to remove the bottom-most group.

Above each group was a couple of menubuttons that had the choices of "AND items that match..." / "OR items that match..." (except for the first group which varied slightly), and "ANY of the following" / "ALL of the following". Each row was type-aware, so if you selected a string for the variable the conditions would be "IS", "IS NOT", "BEGINS WITH" and so on. For integers you would get "IS", "GREATER THAN", etc, and for dates "ON", "BEFORE", "ON OR BEFORE" and so on.

Where you see the word "or" before the second and third row of the first group, that would be "or" if "ANY of the following" was selected, and "and" if "ALL of the following:" was selected to reinforce the choice and make it easier to "read" the dialog.

It wouldn't let you do any conceivable query but I think it covered about 90% of what an average user would want to do and did it in what I thought was a fairly usable way.

_{(source: clearlight.com)}

Answer 2

Some people would argue this is as intuitive as it gets.