Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 8 of 8 Posts

·
Registered
Joined
·
15 Posts
Discussion Starter · #1 ·
Hello ,

I have three tables

1. Company Name Table
2. Address Table
3. Contact Person Name Table

Primary Key of Customer Name is cusid and the other tables are linked to this Company table by this Cus ID .

I have a requirement in which I have to write a query to get data from all these tables .Now it is easily possible if all the three tables have data for one Cusid ie 3 . But the problem is if any one of the table i.e Contact person name for a Cust id =3 THERE IS NO DATA but has address at cust id= 3 my query fails .I know a join can help me to get data from 2 tables but i am not sure how a join will help me to get this data from three tables even though there may not be data in one of the table.
Please HELP !!
 

·
Registered
Joined
·
3,086 Posts
In what way does your query fail? Surely there isn't an error message. If so, then it's probably incorrectly written. Do you mean the query doesn't return the data you expect?

Open the query in Design View, then switch to SQL view. Copy and paste the query string here, please.

chris.
 

·
Registered
Joined
·
15 Posts
Discussion Starter · #3 ·
Hello Actually I mean the query is not returning the results i expect . If i search for an address in a query , if there is no contact person associated with that address , the query wont return that address . I want that address to be returned even if there is no corresponding contact person name in the contact person table .
I have three tables
 

·
Registered
Joined
·
15 Posts
Discussion Starter · #4 ·
SELECT Table_Master_Companies_list.CompanyID, Table_Master_Companies_list.CompanyName, Table_Master_Companies_list.WebAddress, Table_Company_Address.[Billing Address], Table_Company_Address.City, Table_Company_Address.State, Table_Company_Address.ZIP, Table_Company_Address.Country, Table_Company_Address.[PhoneNumber-1], Table_Company_Address.[PhoneNumber-2], Table_Company_Address.[PhoneNumber-3], Table_Company_Address.[PhoneNumber-4], Table_Company_Address.[FaxNumber-1], Table_Company_Address.[FaxNumber-2], Table_Company_Address.[FaxNumber-3], Table_Company_Address.[FaxNumber-4], Table_ContactPerson_Names.[Contact First Name], Table_ContactPerson_Names.[Contact Last Name], Table_ContactPerson_Names.[Contact Title], Table_ContactPerson_Names.[Email Address], Table_ContactPerson_Names.[Ebay User Id], Table_ContactPerson_Names.[Ebay Company Name], Table_Master_Companies_list.Theatre, Table_Master_Companies_list.FolderLink, Table_Master_Companies_list.Comments
FROM Table_ContactPerson_Names inner JOIN (Table_Master_Companies_list inner JOIN Table_Company_Address ON Table_Master_Companies_list.CompanyID = Table_Company_Address.CompanyId) ON Table_ContactPerson_Names.CompanyId = Table_Master_Companies_list.CompanyID
WHERE (((Table_Master_Companies_list.CompanyName) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Master_Companies_list.WebAddress) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[Billing Address]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.City) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.State) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.ZIP) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.Country) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[PhoneNumber-1]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[PhoneNumber-2]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[PhoneNumber-3]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[PhoneNumber-4]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[FaxNumber-1]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[FaxNumber-2]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[FaxNumber-3]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[FaxNumber-4]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_ContactPerson_Names.[Contact First Name]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_ContactPerson_Names.[Contact Last Name]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_ContactPerson_Names.[Contact Title]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_ContactPerson_Names.[Email Address]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_ContactPerson_Names.[Ebay User Id]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_ContactPerson_Names.[Ebay Company Name]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Master_Companies_list.Theatre) Like "*" & [ENTER SEARCH PARAMETER] & "*"))
WITH OWNERACCESS OPTION;
 

·
Registered
Joined
·
3,086 Posts
Okay, well, the answer to your problem is the type of JOIN you're using. An INNER JOIN selects only those records from both tables where all the criteria match. You want a directional JOIN like a LEFT or RIGHT JOIN. A directional join includes all the data from one side of the join regardless of the matching data on the other side. So, for instance if you SELECT FROM your addresses_table and LEFT JOIN contacts_table on address_pk = contact_fk, all the records from the address_table WHERE your criteria fit will be presented along with only those records from the contacts_table that agree with the join clause.

Or, more particularly, perhaps this string will work for you:

SELECT Table_Master_Companies_list.CompanyID, Table_Master_Companies_list.CompanyName, Table_Master_Companies_list.WebAddress, Table_Company_Address.[Billing Address], Table_Company_Address.City, Table_Company_Address.State, Table_Company_Address.ZIP, Table_Company_Address.Country, Table_Company_Address.[PhoneNumber-1], Table_Company_Address.[PhoneNumber-2], Table_Company_Address.[PhoneNumber-3], Table_Company_Address.[PhoneNumber-4], Table_Company_Address.[FaxNumber-1], Table_Company_Address.[FaxNumber-2], Table_Company_Address.[FaxNumber-3], Table_Company_Address.[FaxNumber-4], Table_ContactPerson_Names.[Contact First Name], Table_ContactPerson_Names.[Contact Last Name], Table_ContactPerson_Names.[Contact Title], Table_ContactPerson_Names.[Email Address], Table_ContactPerson_Names.[Ebay User Id], Table_ContactPerson_Names.[Ebay Company Name], Table_Master_Companies_list.Theatre, Table_Master_Companies_list.FolderLink, Table_Master_Companies_list.Comments
FROM Table_ContactPerson_Names RIGHT JOIN (Table_Master_Companies_list inner JOIN Table_Company_Address ON Table_Master_Companies_list.CompanyID = Table_Company_Address.CompanyId) ON Table_ContactPerson_Names.CompanyId = Table_Master_Companies_list.CompanyID
WHERE (((Table_Master_Companies_list.CompanyName) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Master_Companies_list.WebAddress) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[Billing Address]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.City) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.State) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.ZIP) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.Country) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[PhoneNumber-1]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[PhoneNumber-2]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[PhoneNumber-3]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[PhoneNumber-4]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[FaxNumber-1]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[FaxNumber-2]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[FaxNumber-3]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Company_Address.[FaxNumber-4]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_ContactPerson_Names.[Contact First Name]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_ContactPerson_Names.[Contact Last Name]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_ContactPerson_Names.[Contact Title]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_ContactPerson_Names.[Email Address]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_ContactPerson_Names.[Ebay User Id]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_ContactPerson_Names.[Ebay Company Name]) Like "*" & [ENTER SEARCH PARAMETER] & "*")) OR (((Table_Master_Companies_list.Theatre) Like "*" & [ENTER SEARCH PARAMETER] & "*"))
WITH OWNERACCESS OPTION;​
Notice I used a RIGHT JOIN because the left side of the join (the contacts table) is the table you say may not have a foreign key for every address record. Also note I left the INNER JOIN in place between the addresses and master list tables because you apparently have no issue with that relation.

Create a new query and copy my version of the SQL string into the query's SQL design view; see if it gives you the results you want. It may not at that, which brings me to my second point: judging by this query string, your data schema suffers from some normalization issues. For a database of three tables, having a query string longer than--at most--2 or 3 lines (150-250 characters) is indicative of a problem. Part of the issue here is that you're driving the conditional parameters through SQL not a form--and there's nothing wrong with that if you don't mind the maintenance and the query interface. However, having arbitrary phone number and FAX number attributes in a definition devoted to addresses is (from a maintenance standpoint) not optimal. And I have to level with you, if your data set is or becomes very large, this query is going to be sloooow.

So, for small business/limited distribution, you'll probably be okay-ish, even with the normalization problem, but if wide distribution or enterprise use is in your future, you might want to have a look at the table definitions and building a more usable query interface that uses techniques other than ORs to customize the queries.

chris.
 

·
Registered
Joined
·
3,086 Posts
[bump]

I thought I'd be a bit more explicit about what the revised query string accomplishes. It includes only those records from Table_Master_Companies_list and Table_Company_Address for which the CompanyIDs match but displays all those records. Then, it includes only those records from Table_ContactPerson_Names where the CompanyID is included in the above set.

That isn't exactly what you asked for, which is a query that includes every address. I've made assumptions about how you intend your query to behave based on the original JOIN clauses. That is, since you originally joined Table_ContactPerson_Names and Table_Master_Companies_list, an INNER JOIN for the nested clause is apparently the correct method. If you wanted the query to be driven by the addresses table, something like the following might be more appropriate (changes in bold):

FROM Table_ContactPerson_Names RIGHT JOIN (Table_Master_Companies_list RIGHT JOIN Table_Company_Address ON Table_Master_Companies_list.CompanyID = Table_Company_Address.CompanyId) ON Table_ContactPerson_Names.CompanyId = Table_Company_Address.CompanyId
chris.
 

·
Registered
Joined
·
15 Posts
Discussion Starter · #7 ·
Chris ,

Thanks a lot for all your feedback . I was able to make the query work after changing it slightly and get the results .Here is the final query

FROM Table_ContactPerson_Names RIGHT JOIN (Table_Company_Address RIGHT JOIN Table_Master_Companies_list ON Table_Master_Companies_list.CompanyID = Table_Company_Address.CompanyId) ON Table_ContactPerson_Names.CompanyId = Table_Master_Companies_list.CompanyId

Thanks a ton

stealthey_99
 
1 - 8 of 8 Posts
Status
Not open for further replies.
Top