Select from multiple tables matching multiple criteria

Question

Hello,

I am working with 3 tables, trying to pull a list that match certain criteria. I need a list of companies from the companies table, that have an active opportunity (iactive is 1 in opportunities table) AND have a record in the notes table of type order or order2.

 Companies Table
+------+------------------+
| cid  |     ccyname      |
+------+------------------+
| 1c   | Bobs Fish        |
| 2c   | Sheep Counters   |
| 3c   | Old Timers       |
| 4c   | Roasted Nuts     |
+------+------------------+

   Notes Table
+------+--------+--------+
| nid  |ntype   |cid     |
+------+--------+--------+
| 1n   | order  |3c      |
| 2n   | quest  |1c      |
| 3n   | order2 |2c      |
| 4n   | cancel |2c      |
+------+--------+--------+

Opportunities Table
+------+--------+--------+
| oid  |iactive | cid    |
+------+--------+--------+
| 1o   | 1      | 1c     |
| 2o   | 0      | 3c     |
| 3o   | 1      | 3c     |
| 4o   | 1      | 2c     |
+------+--------+--------+

With this example the results should be:

     Results
+----------------+--------+
| ccyname        |  oid   |
+----------------+--------+
| Old Timers     | 3o     |
| Sheep Counters | 4o     |
+----------------+--------+

Which would give me only companies that have both an active opportunity and an order or order2 note.

I have tried building this a few different ways, but I always end up with just one result or with too many. Could I get some help?

This is on SQL Server 2005.

Thanks, John

Answer 1

SELECT DISTINCT cid, ccyname
FROM Companies c
INNER JOIN Notes n ON n.cid = c.cid AND n.ntype = 'order2' OR n.ntype = 'order'
INNER JOIN Opportunities o ON o.cid = c.cid AND o.iactive = 1

Answer 2

SELECT DISTINCT(ccyname), oid
FROM Companies AS c
INNER JOIN Notes AS n ON c.cid = n.cid
INNER JOIN Opportunities AS o ON c.cid = o.cid 
WHERE n.ntype IN ('order','order2')
AND o.iactive = 1;

Caveat: Not Tested.

Answer 3

SELECT
    C.ccyname, O.oid
FROM
    Companies C
    JOIN
    Opportunities O AND O.cid = c.cid
WHERE
    O.iactive = 1
    AND
    EXISTS (SELECT * FROM Notes N
        WHERE N.ntype IN ('order', 'order2') AND N.cid = c.cid)