CODEWITHSUNDEEP
sqlsql-serversql-server-2008
JohnN
JohnN

Reputation: 1010

cross referencing with queries

I've been working with SQL for about 6 months now. I'm pretty adept when it comes to pulling information from tables and sorting it but when I want to compare or cross reference multiple tables, I get a little more shaky. I mean it should be a simple answer. I'm guessing I'm just not thinking of the correct where clause and probably need a SELECT DISTINCT for one of the tables.

I'm going to try to explain this question as thoroughly as I can without using specific names. The general situation I'm trying to check for is something like this. I've got a toolbox that has an assortment of tools in it. I also have a list of tasks that I might need to do and a list of problems that I might be performing those tasks for and using the tools on. I've got a table for tools that has columns for descriptions of the tools, a unique id for the tools, and a classification for the tools (power tools, manual tools, building materials, etc.). Another table for different tasks. The columns are task descriptions, and a unique id. Primarily, I want to write a query that can show me any tools that might be labeled as power tools but aren't being used for any specific task.

Here's a sample of my code. It has more tables because in reality the data is spread out among more tables than just 3, but the concept should remain the same.

SELECT Tools.ToolDescription,
  Tools.ToolsID,
  Tools.Classification,
  Tasks.TaskDescription,
  Tasks.TaskID,
  Problems.ProblemDescription,
  Problems.ProblemID
FROM [Lots of inner joins that aren't relevant without actual tables]
WHERE Tools.Classification = 'Power Tools'

Upvotes: 0

Views: 66

Answers (2)

TommCatt
TommCatt

Reputation: 5636

If you want the power tools that aren't assigned to any tasks, why do you have Tasks fields in your result set?

SELECT Tools.ToolDescription,
       Tools.ToolsID,
       Tools.Classification
FROM   Tools
WHERE  Tools.Classification = 'Power Tools';

This is a simply query that just returns all the power tools. But simple is an excellent starting point.

Now you want to filter out all the power tools that are assigned to tasks. That's easy to do in SQL. An outer join lets you keep only those unmatched tools. You don't show the relevant tables so let's just say the Tasks table contains a ToolsID field for every tool used in the task.

SELECT  Tools.ToolDescription,
        Tools.ToolsID,
        Tools.Classification
FROM    Tools
left join Tasks
    on  Tasks.ToolID = Tools.ToolID
WHERE   Tools.Classification = 'Power Tools'
    and Tasks.ToolID is null;

Upvotes: 1

Mick Mnemonic
Mick Mnemonic

Reputation: 7956

Even though it's not clear from the question how your tables are linked to each other, it sounds like you need to use the NOT EXISTS construct. For example:

WHERE Tools.Classification = 'Power Tools' 
AND NOT EXISTS
(SELECT 1 FROM Tasks t WHERE t.<column_to_join_on> = Tools.ToolsID)

Upvotes: 0

Related Questions