> Given a table of orders and line items, I want to create a result set
> showing those orders than contain only line items matching a specific
> list.

Read what Joe Celko has written on this subject of "relational
division": http://www.dbmsmag.com/9607d06.html
This is an old article in DBMS Magazine. Study the SQL Puzzle and its
answer.

In your case of Orders and Order_line_Items (each holding the name of a
fruit), a solution might consist of something like the following.

A table in which each row represents such an ad hoc query identified by
e.q. a user connection or a sequence number.

CREATE TABLE q (
q_id INTEGER NOT NULL,
...
CONSTRAINT q_pk PRIMARY KEY (q_id)
);

Another table in which each row holds a single item value (e.g. 'apple'
or 'banana') that should exist in the order line items.

CREATE TABLE r (
q_id INTEGER NOT NULL,
fruit_name fruit_name_domain NOT NULL,
CONSTRAINT r_pk PRIMARY KEY (q_id, fruit_name),
CONSTRAINT r_fk FOREIGN KEY (q-id) REFERENCES q
);

A view that composes a table in which orders appear that at least have
the requested line items.

CREATE VIEW (
order_id,
request_id
) AS
SELECT DISTINCT i.order_id, r.q_id
FROM order_line_items i
JOIN r AS r1 ON i.fruit_name = r.fruit_name
GROUP BY i.order_id, r.q_id
HAVING COUNT(*) = (SELECT COUNT(*)
FROM r2 WHERE r2.fruit_name = r.fruit_name
AND r.q_id = r2.q_id);

I hope you're still with me. The
HAVING COUNT(*) = (SELECT COUNT(*) ...
does the trick. The equal operator can be changed to >= select orders
that contain _at least_ the requested fruits.

> I'm not opposed to creating a view and selecting from that.
>

The user interface should allow users to create a query by selecting a
number of fruits. Note that the query should be inserted into the
database as a row in table q and multiple rows in table r. Then, you
select from the view with the q_id as search argument.

Firebird performance should not be an issue if the query is index
covered on the join arguments. You probably want to join the view with
other tables to surface the more relevant information.

Good luck,
Marco

=========================
Marco Bommeljé
elm: mbommelj @ xs4all.nl
www: bcp-software.nl
=========================