Subqury problem

[mikeyd]

This doesn't work but you know what I am trying to get at I hope.

A result with only some fields that are Distinct.

**********************************************

SELECT CONTSUPP.RECTYPE ( DISTINCT CONTACT1.CONTACT, CONTACT1.COMPANY )

FROM CONTACT1 INNER JOIN CONTACT2 ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO INNER JOIN CONTSUPP ON CONTACT2.ACCOUNTNO = CONTSUPP.ACCOUNTNO

 

[maswien]

I guess what you want to do is to take following rows:

1 john, microsoft
X peter, Oracle

from following rows:

cont.rectype contact1.contact contact1.company
------------ ---------------- ----------------
1, john, microsoft
2, peter, Oracle
3, peter, Oracle

The problem here is that you need to decide the value for
X, 2 or 3?

If you want 2 then SQL you want will be

select contact1.contact, contact1.company,min(contsupp.rectype)
FROM CONTACT1 INNER JOIN CONTACT2 ON CONTACT1.ACCNO = CONTACT2.ACCNO
INNER JOIN CONTSUPP ON CONTACT2.ACCNO = CONTSUPP.ACCNO
group by contact1.contact, contact1.company


If you want 3 then the SQL will be:

select contact1.contact, contact1.company, max(contsupp.rectype)
FROM CONTACT1 INNER JOIN CONTACT2 ON CONTACT1.ACCNO = CONTACT2.ACCNO
INNER JOIN CONTSUPP ON CONTACT2.ACCNO = CONTSUPP.ACCNO
group by contact1.contact, contact1.company