By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,947 Members | 1,548 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,947 IT Pros & Developers. It's quick & easy.

How do you apply PK filters to FK tables?

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

Expand|Select|Wrap|Line Numbers
  1. SELECT    Users.UserID, Users.FirstName, Users.LastName, Users.Age, Nationalities.Nationality, Genders.Gender, NativeLanguages.NativeLanguage, Users.City, States.State, Countries.Country, Users.ApplicationDate
  2. FROM      dbo.Users
  3. left join dbo.Nationalities on dbo.Users.NationalityID = dbo.Nationalities.NationalityID
  4. left join dbo.Genders on dbo.Users.GenderID = dbo.Genders.GenderID
  5. left join dbo.NativeLanguages on dbo.Users.NativeLanguageID = dbo.NativeLanguages.NativeLanguageID
  6. left join dbo.States on dbo.Users.StateID = dbo.States.StateID
  7. left join dbo.Countries on dbo.Users.CountryID = dbo.Countries.CountryID
  8. WHERE      dbo.Users.AccessID = 2
  9.   AND        dbo.Countries.RegionFilter = 'Americas'
  10. 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT    Contacts.ContactID, Contacts.UserID, Contacts.ContactFirstName, Contacts.ContactLastName, UserContacts.Contact, Contacts.City, States.State, Countries.Country, Contacts.Phone,
  2. (Users.FirstName) + ' ' + (Users.LastName) AS TheUser, Users.FirstName, Users.LastName
  3. FROM      dbo.Contacts
  4. left join dbo.Users on dbo.Users.UserID = dbo.Contacts.UserID
  5. left join dbo.UserContacts on dbo.Contacts.UserContactID = dbo.UserContacts.UserContactID
  6. left join dbo.States on dbo.Contacts.StateID = dbo.States.StateID
  7. left join dbo.Countries on dbo.Contacts.CountryID = dbo.Countries.CountryID
I tried adding:

Expand|Select|Wrap|Line Numbers
  1. WHERE Contacts.UserID = Users.UserID 
  2.     AND Users.CountryID = Countries.CountryID
  3.     AND Countries.RegionFilter = 'Americas'
But it doesn't work. Any ideas how I can apply the PK filter to the FK?

Thanks,
JRBower
Mar 15 '08 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
try:

Expand|Select|Wrap|Line Numbers
  1. SELECT    Contacts.ContactID, Contacts.UserID, Contacts.ContactFirstName, Contacts.ContactLastName, UserContacts.Contact, Contacts.City, States.State, Countries.Country, Contacts.Phone,
  2. (Users.FirstName) + ' ' + (Users.LastName) AS TheUser, Users.FirstName, Users.LastName
  3. FROM      dbo.Contacts
  4. left join dbo.Users on dbo.Users.UserID = dbo.Contacts.UserID
  5. left join dbo.UserContacts on dbo.Contacts.UserContactID = dbo.UserContacts.UserContactID
  6. left join dbo.States on dbo.Contacts.StateID = dbo.States.StateID
  7. left join dbo.Countries on dbo.Contacts.CountryID = dbo.Countries.CountryID and  Countries.RegionFilter = 'Americas'
  8.  
-- CK
Mar 17 '08 #2

JRBower
P: 14
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT    CertificateID, Certificates.UserID, Certificate, Institution, CertificationDate, (Users.FirstName) + ' ' + (Users.LastName) AS TheUser, Users.FirstName, Users.LastName
  2. FROM      dbo.Certificates
  3. left join dbo.Users on dbo.Certificates.UserID = dbo.Users.UserID
Hopefully I communicated it better this time. :)

Regards,
James
Mar 17 '08 #3

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

Expand|Select|Wrap|Line Numbers
  1. :::CountryID || Country  || RegionFilter
  2. __1 _________|| America ||   Americas
  3. __2_________ || Canada ||   Americas
  4. __3_________ || England ||   Europa
  5. __4________._|| France. ||   Europa
etc

And of course I can filter FK tables like this:

Expand|Select|Wrap|Line Numbers
  1. WHERE   dbo.Users.CountryID IN ('1', '2', 'etc')
So I can do this:

Expand|Select|Wrap|Line Numbers
  1. SELECT    CertificateID, Certificates.UserID, Certificate, Institution, CertificationDate, (Users.FirstName) + ' ' + (Users.LastName) AS TheUser, Users.FirstName, Users.LastName
  2. FROM      dbo.Certificates
  3. left join dbo.Users on dbo.Certificates.UserID = dbo.Users.UserID
  4. 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:

Expand|Select|Wrap|Line Numbers
  1. 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
Mar 18 '08 #4

ck9663
Expert 2.5K+
P: 2,878
CK,
As a follow up to my initial enquiry I should say that my Countries table is basically layed out like this:

Expand|Select|Wrap|Line Numbers
  1. :::CountryID || Country  || RegionFilter
  2. __1 _________|| America ||   Americas
  3. __2_________ || Canada ||   Americas
  4. __3_________ || England ||   Europa
  5. __4________._|| France. ||   Europa
etc

And of course I can filter FK tables like this:

Expand|Select|Wrap|Line Numbers
  1. WHERE   dbo.Users.CountryID IN ('1', '2', 'etc')
So I can do this:

Expand|Select|Wrap|Line Numbers
  1. SELECT    CertificateID, Certificates.UserID, Certificate, Institution, CertificationDate, (Users.FirstName) + ' ' + (Users.LastName) AS TheUser, Users.FirstName, Users.LastName
  2. FROM      dbo.Certificates
  3. left join dbo.Users on dbo.Certificates.UserID = dbo.Users.UserID
  4. 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:

Expand|Select|Wrap|Line Numbers
  1. 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
Mar 19 '08 #5

JRBower
P: 14
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
Mar 19 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.