'Unable to join tables from 2 tables with many to many condition

I have this diagram below, I am trying to first computes the total number of suppliers supplying the specified part then extracts the supplier’s information supplying the specified part.

SELECT count(*) totalCount, s_suppkey, s_name                                               
FROM supplier INNER JOIN part ON s_suppkey = p_suppkey
WHERE p_partkey = 123
GROUP BY s_suppkey, s_name;

enter image description here But i keep getting this error ORA-00904: "P_SUPPKEY": invalid identifier

sqloracle


Solution 1:[1]

Based on your diagram, there is no such column in the part table,

Solution 2:[2]

You (probably) need to use the (bridging) table that is part-hidden off to the left of your image that connects the parts and suppliers in a many-to-many relationship.

SELECT COUNT(*) OVER () totalCount,
       s_suppkey,
       s_name                                               
FROM   supplier s
WHERE  EXISTS(
         SELECT 1
         FROM   part_supplied_by ps
         WHERE  s.s_suppkey = ps.s_suppkey
         AND    p.p_partkey = 123
       );

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Darek
Solution 2 MT0

Related Questions