sql - cross referencing with queries -

-

i've been working sql 6 months now. i'm pretty adept when comes pulling information tables , sorting when want compare or cross reference multiple tables, little more shaky. mean should simple answer. i'm guessing i'm not thinking of correct clause , need select distinct 1 of tables.

i'm going try explain question thoroughly can without using specific names. general situation i'm trying check this. i've got toolbox has assortment of tools in it. have list of tasks might need , list of problems might performing tasks , using tools on. i've got table tools has columns descriptions of tools, unique id tools, , classification tools (power tools, manual tools, building materials, etc.). table different tasks. columns task descriptions, , unique id. primarily, want write query can show me tools might labeled power tools aren't being used specific task.

here's sample of code. has more tables because in reality data spread out among more tables 3, concept should remain same.

select tools.tooldescription,   tools.toolsid,   tools.classification,   tasks.taskdescription,   tasks.taskid,   problems.problemdescription,   problems.problemid [lots of inner joins aren't relevant without actual tables] tools.classification = 'power tools'

if want power tools aren't assigned tasks, why have tasks fields in result set?

select tools.tooldescription,        tools.toolsid,        tools.classification   tools  tools.classification = 'power tools';

this query returns power tools. simple excellent starting point.

now want filter out power tools assigned tasks. that's easy in sql. outer join lets keep unmatched tools. don't show relevant tables let's tasks table contains toolsid field every tool used in task.

select  tools.tooldescription,         tools.toolsid,         tools.classification    tools left join tasks     on  tasks.toolid = tools.toolid   tools.classification = 'power tools'     , tasks.toolid null;

Comments

Post a Comment

Popular posts from this blog

node.js - Mongoose: Cast to ObjectId failed for value on newly created object after setting the value -

-
this question has answer here: what's mongoose error cast objectid failed value xxx @ path “_id”? 7 answers even though has been asked many times because entity has no valid objectid field. i came across situation objectid set value can casted objectid still error. the schema: var serviceschema = parammongoose.schema({ servicename:{ type: parammongoose.schema.types.objectid , ref: 'servicenames',required:true } }); pseudo code: servicefromdb=new service({servicename:'some name'}); servicefromdb.servicename='000000000000000000000001'; servicefromdb.save(function(paramerror,paramdata){ if(paramerror){ console.log('but but...',servicefromdb,paramerror) } }); the output of code is: but but... { servicename: 000000000000000000000001, _id: 55079a90286f49280364f78b } { [casterror: cast objectid faile...
Read more

gradle error "Cannot convert the provided notation to a File or URI" -

-
i have defined 2 system properties in gradle.properties: systemprop.builddir=build systemprop.cfgdir=build\\cfg and have following task defined in build.gradle: task clean(group:'clean',description:'clean project') << { ant.sequential { delete(dir: system.properties.builddir) mkdir(dir: system.properties.builddir) delete(dir: system.properties.cfgdir) mkdir(dir: system.properties.cfgdir) } } this generates following error: execution failed task ':clean'. > cannot convert provided notation file or uri: {dir=build}. following types/formats supported: - string or charsequence path, e.g 'src/main/java' or '/usr/include' - string or charsequence uri, e.g 'file:/usr/include' - file instance. - uri or url instance. but equivalent block of code not generate error , works expected: task clean(group:'clean',description:'clean project') << { ...
Read more

python - NameError: name 'subprocess' is not defined -

-
i'm trying make rng website: http://jamesdotcom.com/?p=417 . when run following code: captureimage = subprocess.popen( [ "fswebcam", "-r", "356x292", "-d", "/dev/video0", "static.jpg", "--skip", "10" ], stdout=devnull, stderr=devnull) captureimage.communicate() i following error message: nameerror: name 'subprocess' not defined what should do? thanks have imported first? import subprocess captureimage = subprocess.popen(["fswebcam", ...) subprocess included in python standard library since python 2.4. should not necessary install it.
Read more