Posted by Andy Hassall on 07/10/05 02:50

On Sat, 09 Jul 2005 17:35:18 GMT, "Nick Hayworth" <chipped_up@hotmail.com>
wrote:

>I have a suppliers table and a parts table, linked in a many-to-many
>relationship via a suppliers_parts table.
>suppliers_parts has a supplier_id field which links to suppliers.id and a
>part_id field which links to parts.id
>
>given a list of part id's, how can i retrieve a list of suppliers who carry
>ALL the parts?

For example, let the list of part_ids be: 1, 2, 3, 4, 5.
This is 5 separate parts.

One way to approach finding the suppliers that have all of this list is:

select supplier_id
from suppliers_parts
where part_id in (1,2,3,4,5)
group by supplier_id
having count(*) = 5

Another is a 5-way join, one for each of the part IDs.

[Back to original message]