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

The best interface I've seen for this was a home-grown control that drew a tree to clearly show the order of operations. I've never seen a third-party control that did this but I haven't looked for one either.

Answer 2

Microsoft SQL Server has a interface like that, i have used it in SQL Server 2000 but i bet it's in 2005 express too so you can take a look if you want.

Answer 3

Assuming .NET, I'd go with a DataGridView to store each condition and to assign each an ID as it is created and have a textbox that allows you enter the particular query conditions.

You could then, once all conditions are written, allow for combining 2 at a time with either an AND or an OR and then displaying the resulting query for verification

Condition1

Condition2

Condition3

Condition4

Condition5

in your case, once you add each one to your dataset and populate the DataGridView, you would then do (i imagine a form with 3 dropdown boxes, top one and bottom one allow for conditions or "compounds" and the middle dropdown is AND/OR only:

Condition1 AND Condition2 = "Compound1"

Condition1 AND Condition5 = "Compound2"

Compound1 OR Compound2 = "Compound3"

Compound3 AND Condition4 = "Compound4"

 

and compound4 is your final query

make sense?

Answer 4

You can check how MS Access does it. I won't call it intuitive but it is simple.

Answer 5

If this is important enough to spend a lot of time on, I'd consider using Venn diagrams. The visualisation will represent the result sets rather than the query terms. So to demonstrate AND you would show two circles representing the results, and highlight the overlap between them (intersection).

To demonstrate OR you would show the two circles and highlight the union of both.

Then to show the whole multi-part query you can either show five circles with some combination of union and intersection, or else combine each parenthesis and then hide the detail, making the results into a new circle to combine with other elements. Lots of drag-and-drop here, and dynamic resizing of subclauses for clarity.

To make this intuitive and easy to use would take quite some work, but for some applications it would be a really powerful interface.

Answer 6

I used to work on a system where we aligned boolean logic similar to the below.

The right columns (Inner) and (Outer) provide two levels of logic.

Variable       Inner     Outer
Condition1     And
Condition2               Or
Condition1     And
Condition5               And
Condition4               

Or more optimized...

Condition4               And
Condition1               And       
Condition2               Or
Condition5

Answer 7

TheBat! has the best interface for that I personally hit on. (Used for mail sort rules.)

It goes:

Source folder is not one of \\Google\Inbox

AND

Subject ends with "new comment"

OR Subject match "some string"

Answer 8

It is kind of specific to its domain, but f-spot has a nice way of doing this. It is photo management software, and if you click on one of the tags to find pictures by tag, it displays a bar across the top of your search results. You then can drag and drop tags onto that bar, and right click to select negation, and can drag the tags around in the bar to group into and and or clauses. I'm not sure how well that scales for tons of tags (or non-enumerated conditions), but it is dead simple to figure out and nicely interactive.

Answer 9

Upvote the funny answers, don't downvote them!