• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

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
0
jtbrown1111
Asked:
jtbrown1111
3 Solutions
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
jtbrown1111Author 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
 
jtbrown1111Author Commented:
@ScottPletcher,

Perfect,   that worked ....    Thanks for the help everyone ....  I know enough to be dangerous without all of you ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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