We help IT Professionals succeed at work.

Query

mrk_raj
mrk_raj asked
on
Hai experts,
   I have a table 'PersonProducts' which contains the Products that can be sold by each sales persons.

The structure of the table is like this...

prnprod_id    int  primary Key
prnprod_prn   int  (references prn_id in Person table )
prnprod_prod  int  (references to prod_id in Products table )

I want to get the list of products (prnprod_prod) that are common to all the persons (prnprod_prn).

Can you send me the query which performs this...
Comment
Watch Question

Commented:
select *
from Person Pers,Product Prod, PersonProduct PersProd
where PersProd.prnprod_prn=Pers.prn_id and PersProd.prnprod_prod=Prod.prod_id

Author

Commented:
hai ntony,

that is not I want.I want to get the products  that are commnon to a given set of sales persons.
CERTIFIED EXPERT
Top Expert 2014
Commented:
Select PersProd, Count(PersProd)
From PersonProducts
Group By PersProd, Count(PersProd)
Having Count(PersProd) = (Select Count(*) From Person )


==========================================
If you know the number of persons (perhaps in a separate query), you can supply that number instead of the sub-query and it will run much faster.

Author

Commented:
thank you  for sending me the answer

Explore More ContentExplore courses, solutions, and other research materials related to this topic.