Skip to main content
tclaremont
tclaremont
Inspiring
May 1, 2008
Question

Simple Query Help Please

  • May 1, 2008
  • 3 replies
  • 350 views
I have two related tables. tblVisits, and tblProcedure. Each Visit has one or more procedures, linked on VisitIDNo.

What is the syntax to return ALL the visits, and only the FIRST procedure (CPT4)?

<cfquery name="GetAll" datasource="PaceAce">
SELECT tblVisits.VisitIDNo, tblProcedure.VisitIDNo, tblProcedure.CPT4
FROM tblVisits
JOIN tblProcedure ON tblVisits.VisitIDNo = tblProcedure.VisitIDNo
</cfquery>
    This topic has been closed for replies.

    3 replies

    tclaremont
    tclaremontAuthor
    Inspiring
    May 2, 2008
    Long story, but we are not a typical health organization. I agree that just about every other health care environment would dictate a primary procedure. I have been fighting that battle for years.
    D
    Dan_Bracuk
    Inspiring
    May 1, 2008
    I'm wondering why your business rules don't compel you to identify a primary procedure.
    tclaremont
    tclaremontAuthor
    Inspiring
    May 1, 2008
    I solved it by using DISTINCT.

    Not sure if that is the textbook way, though.

    <cfquery name="GetAll" datasource="PaceAce">
    SELECT DISTINCT tblVisits.VisitIDNo, tblProcedure.VisitIDNo, tblProcedure.CPT4
    FROM tblVisits
    JOIN tblProcedure ON tblVisits.VisitIDNo = tblProcedure.VisitIDNo
    </cfquery>
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices