Simple SQL Question (I hope)

Hi Niners,

I have what is probably a very simple SQL problem, but being SQL ignorant, I am having trouble working out the syntax required.  any help would be gratefully received.

I have 3 tables with some example data as shown below.  2 Data tables and a cross reference table that links the data in teh data tables.



1.  Individuals ( 2 columns)

ID      Name
-----------------
1       Mike
2       Alan
3       Doug


2.  Firms (4 columns)

ID   CompanyName     Status      Address
-------------------------------------------
1    Microsoft       Verified    Redmond
2    BBC             Verified    London
3    Ocean Software  Unverified  Manchester
4    Rare            Verified    Twycross

3.  Cross Reference (3 Columns)

IndividualID     FirmID    Level
-----------------------------------
1                1         1
1                3         2
2                1         2
2                1         3
3                4         2
3                4         3


So Basically

"Mike" holds 2 jobs (Levels) at 2 firms (Microsoft and Ocean S/W)
"Alan" holds 2 jobs (levels) at 1 firm (Microsoft)
"Doug" holds 2 jobs (levels) at 1 firm (Rare).

What I want is to remove the duplication from the "Alan" and "Doug" records, such that I can return a dataset as follows

Name        Firm              Address
-------------------------------------
Mike        Microsoft         Reading
Mike        Ocean             Manchester
Alan        Microsoft         Reading
Doug        Rare              Twycross

I also want to further ignore any Individuals that are working for firms with a status of "Unverified".

This is probably quite easy to do if you know how, but I dont know how.  I have been playing with the SELECT DISTINCT keywords, but cannot seem to select more that one field using this method.

Any help would be very greatfully received

Thanks

Mike

CREATE VIEW
    IndividualWorkedAtFirm
AS

SELECT
    IndividualID,
    FirmID
FROM
    CrossReference
GROUP BY
    IndividualID, FirmID -- consider two firms or individuals w/ same name

...

SELECT
    Individuals.Name, Firms.CompanyName, Firms.Address
FROM
    Individuals INNER JOIN IndividualWorkedAtFirm ON
        Individuals.IndividualID = IndividualWorkedAtFirm.IndividualID
    INNER JOIN Firms ON
        IndividualWorkedAtFirm.FirmID = Firms.FirmID
WHERE
    Firms.Status = 'Verified'

select i.name, f.firm, f.address
from
(
  select distinct
        individualId,
        firmId
  from crossReference
) u
inner join individuals i on
    i.id=u.individualId
inner join firms f on
   f.id=u.firmId
  and u.status<>'Unverified'

So why doesn't this work?

SELECT DISTINCT
 Individuals.Name,
 Firms.CompanyName,
 Firms.Address
FROM
 Individuals INNER JOIN
 [Cross Reference] cr ON Individials.IndividualID = cr.IndividualID INNER JOIN
 Firms ON cr.FirmID = Firms.FirmID
WHERE
 Firms.Status <> "Unverified"

Is it because Address is a text or ntext field?

Thanks very much fellas, I got my problem working with the advice given in the above answers.

 

Once again, very much appreciated

 

Kind regards

 

Mike