Solved

Sql / Oracle querry help

Posted on 2011-03-15
2
358 Views
Last Modified: 2012-05-11
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
Comment
Question by:gfedz
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 35137988
>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
 
LVL 1

Author Closing Comment

by:gfedz
ID: 35138043
That's exactly what I needed.  Thank you for your time.
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

821 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