Solved

Question about simple SQL query

Posted on 2008-06-16
3
169 Views
Last Modified: 2010-04-21
I am trying to only get records back when the inspector (lbal_inspectedby) is not the same as any of the inspectors in the query.  So,

SELECT * FROM LBAppraisalLocation WHERE lbal_appraisalid=xxxx AND lbal_inspectedby IS NOT THE SAME AS ANY OF THE OTHERS?????

So if the table looked like this
ID    lbal_appraisalid     lbal_inspectedby
1     4000                     2
2     4000                     14
3     4000                     2

then my query should only return
ID    lbal_appraisalid     lbal_inspectedby
1     4000                     2
2     4000                     14

How can I do this?
0
Comment
Question by:saturation
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21797122
not knowhing the exact column names...
select id, lbal_appraisalid, lbal_inspectedby 
  from LBAppraisalLocation l
 where lbal_appraisalid = xxxx
   and not exists ( select null from LBAppraisalLocation i 
                     where i.lbal_appraisalid = l.lbal_appraisalid
                       and i.lbal_inspectedby = l.lbal_inspectedby 
                       and i.id < l.id 
                )

Open in new window

0
 
LVL 7

Expert Comment

by:garethh86
ID: 21797181
SELECT DISTINCT(lbal_inspectedby), *  WHERE Lbal_appraisalid=@appraisalid FROM LBAppraisalLocation

Hope this helps!
0
 

Author Closing Comment

by:saturation
ID: 31467743
Great!  Worked like a charm.
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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

791 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