Unable to join tables from 2 tables with many to many condition-CodePudding

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;

But i keep getting this error ORA-00904: "P_SUPPKEY": invalid identifier

CodePudding user response：

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

CodePudding user response：

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
       );