SQL Question

I have two tables, one with appointment records and a field called ref_ID.   I have a second table also with ref_ID.  I've joined the two.  My problem is the second table has multiple records with the same ref_ID and I only want to grab one of them.  How can I do this.

SELECT     *
FROM         vwGenPatApptInfo LEFT OUTER JOIN
                      PM.vwRefDrList ON vwGenPatApptInfo.Appt_Refer_Dr_ID = PM.vwRefDrList.RefDrID
WHERE     (vwGenPatApptInfo.Appt_DateTime BETWEEN CONVERT(DATETIME, '2015-04-01 00:00:00', 102) AND CONVERT(DATETIME,          '2015-4-10 00:00:00', 102)) AND
                      (vwGenPatApptInfo.Appt_Status = 'A') AND (vwGenPatApptInfo.Appt_Type_Abbr IN ('BOTOX', 'CONSLT20', ))
ORDER BY vwGenPatApptInfo.Appt_Refer_Dr_Abbr, vwGenPatApptInfo.Appt_DateTime
Joel BrownIT DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark ElySenior Coldfusion DeveloperCommented:
Use Distinct

ie... SELECT Distinct(ref_ID) AS ref_ID
0
BlueYonderCommented:
SELECT     distinct col1name, col2name, ....
FROM         vwGenPatApptInfo LEFT OUTER JOIN
                      PM.vwRefDrList ON vwGenPatApptInfo.Appt_Refer_Dr_ID = PM.vwRefDrList.RefDrID
WHERE     (vwGenPatApptInfo.Appt_DateTime BETWEEN CONVERT(DATETIME, '2015-04-01 00:00:00', 102) AND CONVERT(DATETIME,          '2015-4-10 00:00:00', 102)) AND
                      (vwGenPatApptInfo.Appt_Status = 'A') AND (vwGenPatApptInfo.Appt_Type_Abbr IN ('BOTOX', 'CONSLT20', ))
ORDER BY vwGenPatApptInfo.Appt_Refer_Dr_Abbr, vwGenPatApptInfo.Appt_DateTime
0
Daniel Van Der WerkenIndependent ConsultantCommented:
You have to limit it by setting the requirement in a sub select.

Like this:

WHERE (blah)
   AND PM.vwRefDrList.RefDrID = (SELECT TOP 1 vwRefDrList.RefDrID FROM PM.vwRefDrList ORDER BY vwRefDrList.RefDrID DESC )

Open in new window


Or something similar.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott PletcherSenior DBACommented:
Use an OUTER APPLY.  I also changed the date testing to a more reliable and consistent method.  I wasn't sure if you want April 10 in the results or not; I guessed yes, but change the end date if you need to.


SELECT     *
FROM         vwGenPatApptInfo OUTER APPLY (
                      SELECT TOP (1) *
                      FROM PM.vwRefDrList
                      WHERE vwGenPatApptInfo.Appt_Refer_Dr_ID = PM.vwRefDrList.RefDrID
                      --ORDER BY ?
                      ) AS outer_apply_1
WHERE     (vwGenPatApptInfo.Appt_DateTime >= '20150401' AND
                   vwGenPatApptInfo.Appt_DateTime <  '20150411') AND /*20150410??*/
                          (vwGenPatApptInfo.Appt_Status = 'A') AND
                          (vwGenPatApptInfo.Appt_Type_Abbr IN ('BOTOX', 'CONSLT20'))
ORDER BY vwGenPatApptInfo.Appt_Refer_Dr_Abbr, vwGenPatApptInfo.Appt_DateTime
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joel BrownIT DirectorAuthor Commented:
@ ScottPletcher,

Your method works for me but I'm not able to select any fields from the PM.vwRedDrList table ......    Any thoughts ?
0
Scott PletcherSenior DBACommented:
You need to reference them using the alias from the OUTER APPLY, not the original table name.  I used "outer_apply_1" as the alias, but naturally you change it to use any unique alias you want.

SELECT     vwGenPatApptInfo.<col_name>, vwGenPatApptInfo.<col_name2>,
    outer_apply_1.RefDrID, outer_apply_1.<other_column_name>, ...
FROM ...
0
Joel BrownIT DirectorAuthor Commented:
@ScottPletcher,

Perfect,   that worked ....    Thanks for the help everyone ....  I know enough to be dangerous without all of you ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.