Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

Sql / Oracle querry help

I am trying to get everyones name and number from the SIDPERS database  that exists in my SQL table with a intRosterID = 4.  When I querry this code below, it gives me everyone in the SIDPERS database, it does not show only the people in my SQL table.  Any ideas on how to tweak this?
SELECT RTRIM(SUBSTRING(GR_ABBR_CODE, 1, 3)) + ' ' + RTRIM(NAME_IND) AS NAME, SSN_SM 
FROM OPENQUERY(SIDPERS, 'SELECT s.GR_ABBR_CODE, s.SSN_SM, p.NAME_IND FROM PERS_SVCMBR_TBL s, PERS_PERSON_TBL p
WHERE s.SSN_SM = p.SSN_SM') WHERE EXISTS (SELECT intMemberID FROM tblMember WHERE intRosterID = 4) ORDER BY SSN_SM

Open in new window

0
gfedz
Asked:
gfedz
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>WHERE EXISTS (SELECT intMemberID FROM tblMember WHERE intRosterID = 4) ORDER BY SSN_SM
will only check if there is at least 1 row with that condition, it does not link to the returned rows actually.

the condition on SSN_SM is guessed... you might need to change the condition as needed
SELECT RTRIM(SUBSTRING(GR_ABBR_CODE, 1, 3)) + ' ' + RTRIM(NAME_IND) AS NAME, SSN_SM 
FROM OPENQUERY(SIDPERS, 'SELECT s.GR_ABBR_CODE, s.SSN_SM, p.NAME_IND FROM PERS_SVCMBR_TBL s, PERS_PERSON_TBL p
WHERE s.SSN_SM = p.SSN_SM') sq
WHERE EXISTS (SELECT null FROM tblMember m WHERE m.intRosterID = 4 AND m.intMemberID = sq.SSN_SM ) 
ORDER BY sq.SSN_SM

Open in new window

0
 
gfedzAuthor Commented:
That's exactly what I needed.  Thank you for your time.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now