Which sort of JOIN to use?

[danb]

Hi,

I have a table of registered users with the following structure:

tblRegistrant:

Id | Name | Address | Email
-------------------------------


I have two other tables, one containing the users' email preferences, and another containing the users' SMS texting preferences:

tblPreferencesEmail:

Id | RegistrantId | Location | PriceMin | PriceMax
-----------------------------------------------------


tblPreferencesSMS:

Id | RegistrantId | Location | PriceMin | PriceMax
-----------------------------------------------------


I want to join the data, so that I can get the preferences and registrant details of each registrant. Not every registrant will necessarily have an entry in either of the preferences tables.

How can I do this with an SQL JOIN? I'm using ASP and Access.

Any help would be great, thanks...

 

[krunalprajapati]

make a UNION of the two table tblPreferencesEmail and tblPreferencesSMS to get data for the registered users, and then make LEFT JOIN with the tblRegistrant tabel, so that U can get all registered users data with the email preferences and SMS texting preferences.

 

[SQL_Hell]

dont bother with a union just left join both tables, unions are nasty and slow

 

[danb]

Thanks folks,

The LEFT JOINs seem to work.

However, I'm having some difficulty in displaying the results from the recordset. The Email and SMS preferences tables contain pretty much the same headings, so how do I return these values from both tables?

Currently my code is as follows (the table fields/names are slightly different to my simplified first post):

set rsGetProfile = Connection.execute("SELECT A.*, B.*, C.* FROM ((tblRegistration A LEFT JOIN tblSubscription_Email B ON (A.Id = B.RegistrationId)) LEFT JOIN tblSubscription_SMS C ON (A.Id = C.RegistrationId)) WHERE A.Email='" & session("Email") & "';")


'General variables
  strPassword = rsGetProfile("Password")
  strFirstName = rsGetProfile("FirstName")
  strSurname = rsGetProfile("Surname")
  strAddress1 = rsGetProfile("Address1")
  strAddress2 = rsGetProfile("Address2")
  strTown = rsGetProfile("Town")
  strCounty = rsGetProfile("County")
  strPostcode = rsGetProfile("Postcode")
  strTel = rsGetProfile("Tel")
  strMobile = rsGetProfile("Mobile")
 'Email subscription variables
  strLocationId_Email = rsGetProfile("LocationId")
  strPropertyTypeId_Email = rsGetProfile("PropertyTypeId")
  strPriceMin_Email = rsGetProfile("PriceMin")
  strPriceMax_Email = rsGetProfile("PriceMax")
  blnNotification_Sale_Email = rsGetProfile("Notification_Sale")
  blnNotification_Auction_Email = rsGetProfile("Notification_Auction")
 'SMS subscription variables
  strLocationId_SMS = rsGetProfile("LocationId")
  strPropertyTypeId_SMS = rsGetProfile("PropertyTypeId")
  strPriceMin_SMS = rsGetProfile("PriceMin")
  strPriceMax_SMS = rsGetProfile("PriceMax")
  blnNotification_Sale_SMS = rsGetProfile("Notification_Sale")
  blnNotification_Auction_SMS = rsGetProfile("Notification_Auction")

As you can see, I need to return values from the Email and SMS tables, which have the same column headings.

I've tried:

strLocationId_Email = rsGetProfile("B.LocationId")
strLocationId_SMS = rsGetProfile("C.LocationId")

But I get an 'item cannot be found relating to the requested name or ordinal' error.

Any ideas, without having to rename the table column headers so they're unique when joined together?

Surely this must be possible??

Thanks for your help with this, much appreciated...