4

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)

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
user1165334
  • 43
  • 1
  • 3
  • [This article](http://stackoverflow.com/questions/2302873/sql-syntax-error-with-intersect) shows you how to generically rewrite INTERSECT queries with JOINs. Specifically, see Vinko Vrsalovic's solution. – mwigdahl Jan 23 '12 at 16:29

6 Answers6

2

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
rauschen
  • 3,956
  • 2
  • 13
  • 13
1

Here is an alternative approach that is relationally equivalent (i.e. eliminating duplicate rows):

SELECT P.Name 
  FROM P
 WHERE EXISTS (
               SELECT * 
                 FROM PC
                WHERE P.Id = PC.Id
                      AND PC.Code ='A' 
                      AND PC.Val > 100
              )
      AND EXISTS (
                  SELECT * 
                    FROM PC
                   WHERE P.Id = PC.Id
                         AND PC.Code ='B' 
                         AND PC.Val > 80
                 );

Here are a couple of alternatives that are semantically equivalent (in that they may return duplicate rows):

SELECT P.Name 
  FROM P, PC
 WHERE P.Id = PC.Id
       AND PC.Code ='A' 
       AND PC.Val > 100
       AND P.Name IN (
                      SELECT P1.Name 
                        FROM P AS P1, PC AS PC1
                       WHERE P1.Id = PC1.Id
                         AND PC1.Code = 'B' 
                             AND PC1.Val > 80
                     );
SELECT P.Name 
  FROM P, PC
 WHERE P.Id = PC.Id
       AND PC.Code ='A' 
       AND PC.Val > 100
       AND P.Name = ANY (
                         SELECT P1.Name 
                           FROM P AS P1, PC AS PC1
                          WHERE P1.Id = PC1.Id
                            AND PC1.Code = 'B' 
                                AND PC1.Val > 80
                        );
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
0
SELECT P.Name
  FROM P
  JOIN PC AS P1 ON P.Id = P1.Id AND P1.Cod = 'A' AND P1.Val > 100
  JOIN PC AS P2 ON P.Id = P2.Id AND P2.Cod = 'B' AND P2.Val >  80

Using table aliases P1 and P2 allows you to do a 3-way join. It isn't quite a self-join, though; not this time.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
0
Select p.Name
from P p
inner join PC pc1 on p.Id = pc1.Id and pc1.Cod = 'A' and pc1.Val > 100
inner join PC pc2 on p.Id = pc2.Id and pc2.Cod = 'B' and pc2.Val > 80
aF.
  • 64,980
  • 43
  • 135
  • 198
0
Select P.Name 
  from P, PC
 where P.Id=PC.Id
   and PC.Cod='A' and Val>100
   and exists (Select 1 From PC Where Id = P.Id and Cod = 'B' and Val > 80)
StevieG
  • 8,639
  • 23
  • 31
0

Wouldn't actually use this but an alternative...

SELECT P.Name
FROM   P
       JOIN PC
         ON P.Id = PC.Id
WHERE  PC.Cod IN ( 'A', 'B' )
       AND Val > 80
GROUP  BY P.Id,
          P.Name
 HAVING MAX(CASE WHEN PC.Cod='A' and Val>100 THEN 1 END) = 1 
 AND MAX(CASE WHEN PC.Cod='B' and Val>80 THEN 1 END) = 1

Or for Microsoft Access the having clause would need to be

 HAVING MAX(IIf(PC.Cod='A' and Val>100, 1, 0)) = 1 
 AND MAX(IIf(PC.Cod='B' and Val>80, 1, 0)) = 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845