Solved

Select query not Pulling correct manner

Posted on 2008-06-25
3
187 Views
Last Modified: 2010-04-21
First off, this query works but not the way I want it too.  I am trying to list all people eligible for an award and then look into another database tally thier existing awrds of this type.  Problem is when i put in the last JOIN it only show me people who have an exisiting award and those whom need one as well.  So if they have one fine tally it but if not it would return nothing but a o or 1st award.  here is the query

select      u.orgstrUname,
      s.sidstrNAME_IND,
      s.sidstrGR_ABBR_CODE,
      p.strSSN,
                     min(p.dtattendance) as B,
      max(p.dtattendance) as E,
      a.awnintnbrgrnt as Total       (this is the line that shows # of awards
from      cms.dbo.tblSIDPERS as s INNER JOIN
      cms.dbo.tblOrganization as u on u.orgstrUPC = s.sidstrCURR_UPC INNER JOIN
      saddotnet.dbo.tblAssignedPersonnel as p on p.strSSN = s.sidstrSSN_SM INNER JOIN
      cms.dbo.tblAwardsNormal as a on p.strSSN = a.awnstrSSN
where      p.bitPresent = 1 and
      p.intUICID in (select intUICID from tblUIC where intTaskForceID = '36' and strUIC = 'P7KAA')
      and a.awnstrawdcat like '%MNSADR%'     ?????(when i add this is when it only gives me 1 or 2)
group by sidstrNAME_IND, strSSN, u.orgstrUName, s.sidstrGR_ABBR_CODE, a.awnintnbrgrnt
0
Comment
Question by:kdeutsch
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
Terry Woods earned 250 total points
ID: 21870165
I'm having a little trouble understanding what you want due to the way you've worded your question. It sounds like adding the last join (INNER JOIN cms.dbo.tblAwardsNormal as a on p.strSSN = a.awnstrSSN) stopped all the records showing that you wanted to see?

If that's the case, try changing the "INNER JOIN" to a "LEFT OUTER JOIN" so that you get all the results from cms.dbo.tblSIDPERS, cms.dbo.tblOrganization, and saddotnet.dbo.tblAssignedPersonnel no matter whether you get a match with the filter "p.strSSN = a.awnstrSSN".

Also, you'll probably need to shift the filter "a.awnstrawdcat like '%MNSADR%' " into  the LEFT OUTER JOIN filter

ie
select u.orgstrUname,
       s.sidstrNAME_IND,
       s.sidstrGR_ABBR_CODE,
       p.strSSN,
       min(p.dtattendance) as B,
       max(p.dtattendance) as E,
       a.awnintnbrgrnt as Total
from cms.dbo.tblSIDPERS as s
     INNER JOIN cms.dbo.tblOrganization as u on u.orgstrUPC = s.sidstrCURR_UPC 
     INNER JOIN saddotnet.dbo.tblAssignedPersonnel as p on p.strSSN = s.sidstrSSN_SM 
     LEFT OUTER JOIN cms.dbo.tblAwardsNormal as a on p.strSSN = a.awnstrSSN
                                                  and a.awnstrawdcat like '%MNSADR%'
where p.bitPresent = 1
  and p.intUICID in (select intUICID from tblUIC where intTaskForceID = '36' and strUIC = 'P7KAA')
group by sidstrNAME_IND, strSSN, u.orgstrUName, s.sidstrGR_ABBR_CODE, a.awnintnbrgrnt

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 21874042
Hi
Thanks for the Solution, I will have to learn more about Joins and how they work.
 I am trying to pull all the records in tblAssignedPersonnel that meet the where clause of bitpresent = 1.  This tells me they where there.  Then I go and get there name based off of ssn in tbl sidpers and tblOrganization pulls whom they belong to.  Table awards is where the exsiting awards reside and I just am looking to see if they have any and then show the count of this specific award.
Hope this clarifies it.  
0
 

Author Closing Comment

by:kdeutsch
ID: 31470734
Thanks, works great.
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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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. …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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