Solved

Select query not Pulling correct manner

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
how to fix this error 14 57
order a table based on column relationship (parent-child), can it be done? 4 41
the whoisactive update 12 39
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 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

23 Experts available now in Live!

Get 1:1 Help Now