Comment Feed for Simple SQL Question (I hope) (TechOff on Channel 9)

Re: Simple SQL Question (I hope)

m1keread — Wed, 31 Jan 2007 20:40:06 GMT

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

Re: Simple SQL Question (I hope)

Flatliner — Wed, 31 Jan 2007 13:12:08 GMT

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?

Re: Simple SQL Question (I hope)

vzczc — Tue, 30 Jan 2007 23:25:39 GMT

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'

Re: Simple SQL Question (I hope)

Matthew van Eerde — Tue, 30 Jan 2007 22:30:28 GMT

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'