Subject Re: [firebird-support] SQL headache
Author Dimitry Sibiryakov
On 28 Jun 2005 at 17:40, Nick Upson wrote:

>Say there is a sales order number 93, the SOL sales order lines are
>for 2 keyboards, 1 mouse, 30 cdroms. I need to know all other sales
>orders which have the same, and only those, products.

Let me suggest this query:

SELECT number from SO where not exists
(select * from SOL SOL1 left join SOL SOL2
on SOL1.item_type=SOL2.item_type
and SOL1.item_quantity=SOL2.item_quantity
and SOL2.sales_number=SO.number
where SOL1.sales_number=93 and SOL2.sales_number is null
)
and not exists
(select * from SOL SOL1 right join SOL SOL2
on SOL1.item_type=SOL2.item_type
and SOL1.item_quantity=SOL2.item_quantity
and SOL1.sales_number=93
where SOL2.sales_number=SO.number and SOL1.sales_number is null
)
--
SY, Dimitry Sibiryakov.