Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL subqueries alternative to INTERSECT

I have two tables: P and PC (master/detail joined by the column Id)

Table P:
Id integer
Name varchar(12)

Table PC:
Id   integer
Code varchar(12)
Val  number

I want to get all Names from P that satisfy the following simultaneous conditions:

  • have a PC with PC.Code='A' and Val>100

  • have another PC with PC.Code='B' and Val>80

In summary, I'm only interested in those P.Name where the details comply with both conditions. Is there a way to select without resorting to INTERSECT?

The INTERSECT query is:

Select P.Name 
  from P, PC
 where P.Id=PC.Id
   and PC.Code='A' and Val>100
INTERSECT
Select P.Name 
  from P, PC
 where P.Id=PC.Id
   and PC.Code='B' and Val>80

(The interest is to check performance and also to allow the query to be run in Access)

like image 871
user1165334 Avatar asked Sep 01 '25 03:09

user1165334


1 Answers

Do not know how the performance is .. try it ..

SELECT P.Name 
  FROM P
  INNER JOIN PC AS a ON P.Id=a.Id and a.Cod='A' and a.Val>100
  INNER JOIN PC AS b ON P.Id=b.Id and a.Cod='B' and a.Val>80
like image 178
rauschen Avatar answered Sep 02 '25 16:09

rauschen