Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Select query not Pulling correct manner

Posted on 2008-06-25
3
Medium Priority
?
195 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
Terry Woods earned 1000 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

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

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

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.…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

715 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