June 12, 2012

Suppliers who can Supply Same Parts

Problem

The table below is about a supplier and the parts he can supply. The sno field in the table refers to the number of suppliers and pno refers to the number of parts.

The key to this problem is to find out all pairs of suppliers who can supply the same parts.


Tip

Rough train of thought: First, group the table by supplier. To find out identical groups, traverse and loop can solve this problem. However, the grouping method in esProc can solve this problem in a much more convenient way. Group the supplier group by parts number, and merger the groups with identical parts. Then, we will get the suppliers supplying the same parts.
  • First, group the table by supplier and create a new table sequence for an easier comparison. Sort the parts number well in a sequence and put them in a new field.
  • Based on the above steps, group the table again by the pno field. The duplicate suppliers will be gathered into one group.
  • Select the groups whose group numbers are more than 1. The result will be the suppliers who can supply the same parts.

Code


Result

As below table indicates, suppliers 2, 3 and 4 is the desired result, as well as the suppliers 5 and 9.