Solved

Limiting a query based on entries in a table?

Posted on 2008-10-29
2
175 Views
Last Modified: 2012-05-05
I have the following:

Select O.ID,O.EndDate,fs.fieldid,fs.type,fs.fieldvalue,fs.description
from Objects O
Inner JOIN metadata fs on fs.ID = O.ID
Inner JOIN DestructionList MS ON  ms.ID = O.ID
where O.Status = 5 AND MS.ID IS NOT NULL AND fs.Type = 'DataTable'

I have 3 tables

Objects
Metadata
DestructionList

The destructionlist tracks the ObjectID and a value for that object which is to be detroyed.

Objects & MetaData stores information.

So what I am trying to do is to join

Objects & MetaData where ID's match

and then join DestructionList where the ID or the Value are not contained within the destructionlist. There are instances where I may have multiple ObjectID's which are the same, but the value's are different, so I need to make sure that I show objects which are not in the destructionlist.

Any help would be much appreciated.
0
Comment
Question by:directxBOB
2 Comments
 
LVL 5

Accepted Solution

by:
jfmador earned 500 total points
ID: 22833338
if you want to get the Objects that are not in DestructionList, you can use a left join between the two table and after the join get only the rows where your DestructionListID is null

Select O.ID,O.EndDate,fs.fieldid,fs.type,fs.fieldvalue,fs.description
from Objects O
Inner JOIN metadata fs on fs.ID = O.ID
LEFT JOIN DestructionList MS ON  ms.ID = O.ID
where O.Status = 5 AND MS.ID IS NULL AND fs.Type = 'DataTable'
0
 

Author Comment

by:directxBOB
ID: 22839010
Select O.ID,O.EndDate,fs.fieldid,fs.type,fs.fieldvalue,fs.description
from Objects O
Inner JOIN metadata fs on fs.ID = O.ID
LEFT JOIN DestructionList MS ON  ms.ID = O.ID and MS.fieldId = fs.FieldID
where O.Status = 5 AND MS.FieldID IS NULL AND fs.Type = 'DataTable'

Cheers for the help, I was just missing two parts. One in the left join where I am joining on both fields, and then in the where .

0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question