Help with JOIN (or UNION?)

[mdsoren]

I am a real "green horn" when it comes to writing SQL statements. Can anyone help with what I see as a difficult JOIN query?

I have 5 different fields users can select a value in to build their search query. They can choose 1 field, all 5 fields or any combination in between. Given their choices I have to find all records in the database which match ALL their elected choices.

I have been able to write each individual SELECT query for anyone of the fields selected, but don't know how to combine multiple queries. Do I use a UNION maybe?

Depending on which fields they want to filter on there could be anywhere from 2 to 5 tables drawn from in the database.

Here are three of the five individual SELECTS which work fine for each of their respective fields:

(1) looking for individual's name anywhere in database...

SELECT DISTINCT tblCorp.ID,
(SELECT FullName FROM tblAddressBook WHERE tblCorp.ID = tblAddressBook.ID) AS EntityName
FROM tblCorp LEFT JOIN tblOfficers ON (tblCorp.ID = tblOfficers.ForeignKey)
WHERE tblCorp.PrimaryContact = intIndividual OR tblCorp.SecondaryContact = intIndividual OR tblOfficers.Name = intIndividual
UNION
SELECT DISTINCT tblCorp.ID,
(SELECT FullName FROM tblAddressBook WHERE tblCorp.ID = tblAddressBook.ID) AS EntityName
FROM (tblCorp LEFT JOIN tblCapital ON tblCorp.ID = tblCapital.ForeignKey) LEFT JOIN tblCertificates ON tblCapital.ID = tblCertificates.ForeignKey
WHERE tblCertificates.SecurityHolder = intIndividual AND tblCertificates.Outstanding = 'Yes'

(2) looking for a specific attorney's name
SELECT DISTINCT tblCorp.ID,
(SELECT FullName FROM tblAddressBook WHERE tblCorp.ID = tblAddressBook.ID) AS EntityName,
(SELECT tblOrganization.StateOorA FROM tblOrganization WHERE tblOrganization.OrganizedAuthorized = 'O' AND tblOrganization.ForeignKey = tblCorp.ID) AS StateOrganized
FROM tblCorp, tblAddressBook, tblOrganization
WHERE tblCorp.ResponsibleAttorney = intAttorney

(3) looking for all entities in a particular state
SELECT DISTINCT tblCorp.ID, tblOrganization.OrganizedAuthorized,
(SELECT FullName FROM tblAddressBook WHERE tblCorp.ID = tblAddressBook.ID) AS EntityName,
(SELECT FullName FROM tblAddressBook WHERE tblCorp.ResponsibleAttorney = tblAddressBook.ID) AS RespAtty
FROM tblCorp RIGHT JOIN tblOrganization ON (tblCorp.ID = tblOrganization.ForeignKey)
WHERE tblOrganization.StateOorA = 'strState'

Can anyone help?!

TIA,
MDS