[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

T-SQL ANSI Mode

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
SELECT
	tblTools.ToolName,
	tblTools.ToolDesc,
	tblToolCategories.ToolCategoryName,
	tblToolDDR.ToolDDRDesc,
	tblContacts.FName,
	tblContacts.LName,
	tblToolSampleQuestions.Question
FROM
	tblTools,
	tblToolCategories,
	tblToolSampleQuestions,
	tblToolDDR,
	tblContacts,
	tblToolContacts
WHERE
	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

0
cartch2008
Asked:
cartch2008
  • 2
  • 2
1 Solution
 
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
0
 
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.
0
 
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?
0
 
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.
0
 
cartch2008Author Commented:
Any ideas how I can do a full text search on those fields?  See:

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_25480420.html
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now