How do you apply PK filters to FK tables?

[JRBower]

In my main users table I filter job apps to one of four regions. Here's the code for one region:

SELECT    Users.UserID, Users.FirstName, Users.LastName, Users.Age, Nationalities.Nationality, Genders.Gender, NativeLanguages.NativeLanguage, Users.City, States.State, Countries.Country, Users.ApplicationDate
FROM      dbo.Users
left join dbo.Nationalities on dbo.Users.NationalityID = dbo.Nationalities.NationalityID
left join dbo.Genders on dbo.Users.GenderID = dbo.Genders.GenderID
left join dbo.NativeLanguages on dbo.Users.NativeLanguageID = dbo.NativeLanguages.NativeLanguageID
left join dbo.States on dbo.Users.StateID = dbo.States.StateID
left join dbo.Countries on dbo.Users.CountryID = dbo.Countries.CountryID
WHERE      dbo.Users.AccessID = 2
  AND        dbo.Countries.RegionFilter = 'Americas'
ORDER BY  ApplicationDate DESC

There are several other tables joined to the Users table that need the same region filter applied to as well. Here's the EmergencyContact table:

SELECT    Contacts.ContactID, Contacts.UserID, Contacts.ContactFirstName, Contacts.ContactLastName, UserContacts.Contact, Contacts.City, States.State, Countries.Country, Contacts.Phone,
(Users.FirstName) + ' ' + (Users.LastName) AS TheUser, Users.FirstName, Users.LastName
FROM      dbo.Contacts
left join dbo.Users on dbo.Users.UserID = dbo.Contacts.UserID
left join dbo.UserContacts on dbo.Contacts.UserContactID = dbo.UserContacts.UserContactID
left join dbo.States on dbo.Contacts.StateID = dbo.States.StateID
left join dbo.Countries on dbo.Contacts.CountryID = dbo.Countries.CountryID

I tried adding:

WHERE Contacts.UserID = Users.UserID 
    AND Users.CountryID = Countries.CountryID
    AND Countries.RegionFilter = 'Americas'

But it doesn't work. Any ideas how I can apply the PK filter to the FK?

Thanks,
JRBower

[ck9663]

try:

SELECT    Contacts.ContactID, Contacts.UserID, Contacts.ContactFirstName, Contacts.ContactLastName, UserContacts.Contact, Contacts.City, States.State, Countries.Country, Contacts.Phone,
(Users.FirstName) + ' ' + (Users.LastName) AS TheUser, Users.FirstName, Users.LastName
FROM      dbo.Contacts
left join dbo.Users on dbo.Users.UserID = dbo.Contacts.UserID
left join dbo.UserContacts on dbo.Contacts.UserContactID = dbo.UserContacts.UserContactID
left join dbo.States on dbo.Contacts.StateID = dbo.States.StateID
left join dbo.Countries on dbo.Contacts.CountryID = dbo.Countries.CountryID and  Countries.RegionFilter = 'Americas'
 

-- CK

[JRBower]

Hi CK,
Thanks again for your time. Perhaps I should have used a different table for my example. The suggestion you gave works as I would have expected, however, the contacts table is a special case where I need to sort on the country that is associated with the UserID for the Users table (dbo.Users on dbo.Users.UserID = dbo.Contacts.UserID) and not the ContactID (Contacts.ContactID) of the UserContact table. The reason in this case is that a contact might live in a different country than the user (e.g., I (UserID) live in America but my family (ContactID) live in the Canada.

The Users table has a one to many relationship to the Contacts table, I also enforce referential integrity including using cascade updates and cascade deletes of related records.

What I wanted to achieve in my original post was to be able to filter a table like the Certificates table via the FK based on the PK of the users table. So if the Users table filter (dbo.Countries.RegionFilter = 'Americas') sends my application to the "Americas" region for processing I aslo want the related records within the Contacts table and Certificates table to be filtered to the 'Americas' also.

In the following table users can enter the type of training certificates they have. So I'm wondering how I can filter this (via the FK) so any related table records go where I want them to go.

SELECT    CertificateID, Certificates.UserID, Certificate, Institution, CertificationDate, (Users.FirstName) + ' ' + (Users.LastName) AS TheUser, Users.FirstName, Users.LastName
FROM      dbo.Certificates
left join dbo.Users on dbo.Certificates.UserID = dbo.Users.UserID

Hopefully I communicated it better this time. :)

Regards,
James

[JRBower]

CK,
As a follow up to my initial enquiry I should say that my Countries table is basically layed out like this:

:::CountryID || Country  || RegionFilter
 __1 _________|| America ||   Americas
 __2_________ || Canada ||   Americas
 __3_________ || England ||   Europa
 __4________._|| France. ||   Europa

etc

And of course I can filter FK tables like this:

WHERE   dbo.Users.CountryID IN ('1', '2', 'etc')

So I can do this:

SELECT    CertificateID, Certificates.UserID, Certificate, Institution, CertificationDate, (Users.FirstName) + ' ' + (Users.LastName) AS TheUser, Users.FirstName, Users.LastName
FROM      dbo.Certificates
left join dbo.Users on dbo.Certificates.UserID = dbo.Users.UserID
 WHERE   dbo.Users.CountryID IN ('1', '2', 'etc')

which works fine but, unfortunately, it requires that I list up to 70 CountryID's for one region instead of the simpler:

WHERE   dbo.Countries.RegionFilter = 'Americas'

I'm just not sure how to write the SQL that would make dbo.Countries.RegionFilter = 'Americas' possible in queries in which dbo.Users.CountryID IN ('1', '2', 'etc') has no problem.

James

[ck9663]

CK,
As a follow up to my initial enquiry I should say that my Countries table is basically layed out like this:

:::CountryID || Country  || RegionFilter
 __1 _________|| America ||   Americas
 __2_________ || Canada ||   Americas
 __3_________ || England ||   Europa
 __4________._|| France. ||   Europa

etc

And of course I can filter FK tables like this:

WHERE   dbo.Users.CountryID IN ('1', '2', 'etc')

So I can do this:

SELECT    CertificateID, Certificates.UserID, Certificate, Institution, CertificationDate, (Users.FirstName) + ' ' + (Users.LastName) AS TheUser, Users.FirstName, Users.LastName
FROM      dbo.Certificates
left join dbo.Users on dbo.Certificates.UserID = dbo.Users.UserID
 WHERE   dbo.Users.CountryID IN ('1', '2', 'etc')

which works fine but, unfortunately, it requires that I list up to 70 CountryID's for one region instead of the simpler:

WHERE   dbo.Countries.RegionFilter = 'Americas'

I'm just not sure how to write the SQL that would make dbo.Countries.RegionFilter = 'Americas' possible in queries in which dbo.Users.CountryID IN ('1', '2', 'etc') has no problem.

James

Let's follow your code:


SELECT CertificateID, Certificates.UserID, Certificate, Institution, CertificationDate, (Users.FirstName) + ' ' + (Users.LastName) AS TheUser, Users.FirstName, Users.LastName
FROM dbo.Certificates
left join dbo.Users on dbo.Certificates.UserID = dbo.Users.UserID
WHERE dbo.Users.CountryID IN (select countryid from CountriesTable where RegionFilter = 'Americas')

Happy Coding.

-- CK

[JRBower]

A subquery! of course. :)

Thanks!

Let's follow your code:


SELECT CertificateID, Certificates.UserID, Certificate, Institution, CertificationDate, (Users.FirstName) + ' ' + (Users.LastName) AS TheUser, Users.FirstName, Users.LastName
FROM dbo.Certificates
left join dbo.Users on dbo.Certificates.UserID = dbo.Users.UserID
WHERE dbo.Users.CountryID IN (select countryid from CountriesTable where RegionFilter = 'Americas')

Happy Coding.

-- CK