Solved

Select query not Pulling correct manner

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

856 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