Solved

Select query not Pulling correct manner

Posted on 2008-06-25
3
185 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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.…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now