Querying multiple tables in database

I have a database with these tables and columns:

Person

firstname
lastname 
peid

Buyer

peid

Offer

peid  
pno

I am trying to select names (buyer 1 and buyer 2) that have the same pno and different peid.

FIRSTNAME    LASTNAME   PEID     PNO
-------------------------------------------
Robert       Young      0        18
Stephen      Davison    2        32
Tony         Nguyen     16       28
Lily         Roy        32       14
Aaron        Naidoo     50       51
Adam         Jordan     64       32
Arun         Isaacson   68       27
Charles      Mur开发者_开发百科phy     84       23
Adam         Peter      94       27

What I want is to select two buyers that have the same pno and different peid and the pno that they have in common. For example:

Adam Jordan and Stephen Davison have 32 in common. Adam Peter and Arun Isaacson have 27 in common. I'm trying to select them. The above table is the result of one of my queries, not the original table.

From a comment:

This is what I have done so far

select A.firstname || ' ' || A.lastname as Buyer1,
       B.firstname || ' ' || B.lastname as Buyer2
  from person A, person B, buyer One, buyer Two
 where A.peid = One.peid and B.peid = Two.peid
   and (select off.pno  from offer off where off.peid = One.peid) =
       (select off.pno  from offer off where off.peid = Two.peid)
   and (select off.peid from offer off where off.peid = One.peid) <>
       (select off.peid from offer off where off.peid = Two.peid)
 group by A.firstname || ' ' || A.lastname, B.firstname || ' ' || B.lastname;

If your DBMS supports CTE (common table expressions, aka WITH clauses), you can do this more neatly than if it does not. However, many DBMS do not support it, so here's a version without CTEs. It is often easiest to develop (and test) the answer in stages. It is not entirely clear why you need the Buyer table at all. However, we'll assume it is crucial.

We can generate a list of the people and what they bought with a query like:

SELECT P.*, O.pno
  FROM Person AS P
  JOIN Buyer  AS B ON P.peid = B.peid
  JOIN Offer  AS O ON B.peid = O.peid;

Two answer the main query, we need two copies of this result set, joined on the pno column, with the condition that the peid values are different:

SELECT P1.Name, P1.Peid, P2.Name, P2.Peid, P1.Pno
  FROM (SELECT (P.FirstName || ' ' || P.LastName) AS Name, P.Peid, O.Pno
          FROM Person AS P
          JOIN Buyer  AS B ON P.Peid = B.Peid
          JOIN Offer  AS O ON B.Peid = O.Peid
       ) AS P1
  JOIN (SELECT (P.FirstName || ' ' || P.LastName) AS Name, P.Peid, O.Pno
          FROM Person AS P
          JOIN Buyer  AS B ON P.Peid = B.Peid
          JOIN Offer  AS O ON B.Peid = O.Peid
       ) AS P2
    ON P1.Pno = P2.Pno AND P1.Peid < P2.Peid;

The trick using < in the last line is a common one. It ensures that where you have a symmetric query like this, then each pair is only listed once. That is, if Person1 and Person2 both bought the same item, then so did Person2 and Person1. The < condition, though, ensures that only one of the two rows appears. It also ensures that neither the Person1 and Person1 row nor the Person2 and Person2 row appears.

If your DBMS supports CTEs, you can make the sub-query into a named CTE, and then refer to it in the main query.

Approximate syntax:

WITH Purchases AS 
    (SELECT (P.FirstName || ' ' || P.LastName) AS Name, P.Peid, O.Pno
       FROM Person AS P
       JOIN Buyer  AS B ON P.Peid = B.Peid
       JOIN Offer  AS O ON B.Peid = O.Peid
    )
SELECT P1.Name, P1.Peid, P2.Name, P2.Peid, P1.Pno
  FROM Purchases AS P1
  JOIN Purchases AS P2
    ON P1.Pno = P2.Pno AND P1.Peid < P2.Peid;