Can someone please help me write this query in ANSI mode (no, this isn't a homework question...I've never written my queries in ANSI mode, but in this case, I need to)

here is some info, followed by my non-ansi mode query:

tblTools one-to-many with tblToolSampleQuestions
tblTools one-to-many with tblToolDDR
tblTools one-to-one with tblToolCategories
tblTools many-to-many with tblContacts (tblToolContacts is a junction table)

I want to return results even if the there are nothing in tblToolSampleQuestions for the tool, even if there is nothing in tblToolDDR for the tool, and even if there are no contacts assigned to the tool....all outer joins
	tblTools.ToolCategoryID = tblToolCategories.ToolCategoryID
	AND tblTools.ToolID *= tblToolSampleQuestions.ToolID
	AND tblTools.ToolID *= tblToolDDR.ToolID
	AND tblToolContacts.ContactID *= tblContacts.ContactID
	AND tblTools.ToolID *= tblToolContacts.ToolID

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I am not sure if you can do this with ANSI mode...
you might try to put the "left join"
      AND tblToolContacts.ContactID *= tblContacts.ContactID
into a subquery, and left joining to that subquery instead of joining to the 2 tables directly
Shaun KlineLead Software EngineerCommented:
Assuming your question is about writing the SQL in ANSI SQL:
In ANSI SQL, INNER JOIN is used to restrict results that are found in both tables so you can join tblTools and tblToolCategories like this:
FROM tblTools INNER JOIN tblToolCategories ON tblTools.ToolCategoryID = tblToolCategories.ToolCategoryID

Similarly, an OUTER JOIN is used to bring back results from one table and also results from a second table if there is a match. You use the LEFT, RIGHT and FULL keywords to determine which side of the join the match occurs.

For example, you can use LEFT OUTER JOIN (OUTER is optional) to join tblTools to tblToolSampleQuestions like this:
FROM tblTools LEFT OUTER JOIN tblToolSampleQuestions ON tblTools.ToolID = tblToolSampleQuestions.ToolID

So bringing both of those together...

FROM tblTools INNER JOIN tblToolCategories ON tblTools.ToolCategoryID = tblToolCategories.ToolCategoryID
LEFT OUTER JOIN tblToolSampleQuestions ON tblTools.ToolID = tblToolSampleQuestions.ToolID

The rest is a matter of repetition.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cartch2008Author Commented:
Thanks Shaun Kline, this is great...got it working without putting the contact tables in.  How do I deal with the many to many on the tbltools, tblcontacts, and the tbltoolcontacts junction?
Shaun KlineLead Software EngineerCommented:
To handle that table relationship, you can use a combination of joins:

From tblTools
    LEFT OUTER JOIN tblToolContacts
          INNER JOIN tblContacts ON tblToolContacts.ContactID = tblContacts.ContactID
             ON tblTools.ToolID = tblToolContacts.ToolID

This SQL joins tblToolContacts to tblContacts first (matching records from both tables) then joins the results to tblTools, where all records from tblTools are returned and only match rows from joined tables.
cartch2008Author Commented:
Any ideas how I can do a full text search on those fields?  See:

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.